locked
Filtering for banding scenario - PowerPivot 2.0 RRS feed

  • Question

  • Hi,

    I've a banding scenario with a Sales table and a PriceBands table. I've created the band calculated column on the Sales table. But now I need to create a slicer with all the band name values and I could haven't all the related band values on the Sales table.

    Fe, in the PriceBands table I can have the band A, the band B and the band C, but in the Sales table only band A and B. I cannot create a relationship between Sales and PriceBands.

    How can I create a slicer/filter with the all band name values to filter rightly the Sales table?

    Thanks

    Tuesday, June 25, 2013 11:03 AM

Answers

  • I solved this issue with a workaround.

    Inside the PriceBands table I've created two measures, MinPriceBand and MaxPriceBand using the DAX function MIN() and MAX(). Fe MinPriceBand := MIN(PriceBands[Min_Price_Band]).

    Then, inside Sales table I've created a measure to match the SalesAmount with the two measures about, using the CALCULATE and FILTER functions.

    As the last step, I've put the band name colums as a slicer/filter.

    In this scenario, it isn't right to create a calculated column.

    However, in a such scenario it could be interesting to create a relationship between a calculated column in a fact table with a column in a dimension table.

    Thanks

    • Marked as answer by Elvis Long Thursday, July 4, 2013 1:25 AM
    Friday, June 28, 2013 5:34 PM

All replies

  • Hi, I've tried to create a copy of PriceBads table in order to create the relationship between the Sales table and this duplicate table. I can create the relationship without obtaining a circular dependence error, but the deriving filter/slicer with all band name values has no effects on the Sales powerpivot table.

    Any helps to me, please?

    Thanks

    Tuesday, June 25, 2013 3:50 PM
  • Has anybody else seen this?

    Thanks!


    Ed Price (a.k.a User Ed), SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Thursday, June 27, 2013 7:52 PM
  • I solved this issue with a workaround.

    Inside the PriceBands table I've created two measures, MinPriceBand and MaxPriceBand using the DAX function MIN() and MAX(). Fe MinPriceBand := MIN(PriceBands[Min_Price_Band]).

    Then, inside Sales table I've created a measure to match the SalesAmount with the two measures about, using the CALCULATE and FILTER functions.

    As the last step, I've put the band name colums as a slicer/filter.

    In this scenario, it isn't right to create a calculated column.

    However, in a such scenario it could be interesting to create a relationship between a calculated column in a fact table with a column in a dimension table.

    Thanks

    • Marked as answer by Elvis Long Thursday, July 4, 2013 1:25 AM
    Friday, June 28, 2013 5:34 PM