locked
slicer inactive but still working RRS feed

  • Question

  • The case is that I want to remove data from an incomplete month from PowerPivot and I have a period table (month based) so I created this DAX expression :


    SumNetRevenue:=IF(MAX(DimPeriodMonth[PeriodMonthID]) <= CALCULATE(MAX(FactSalesPerMonth[PeriodMonthID]) -1;ALL(DimPeriodMonth));
    SUM(FactSalesPerMonth[NetRevenue]);
    BLANK()
    )

    And yes it has some flaws (pass a year)  and I have to solve that but the main thing is here that the slicer become inactive because of this expression. Now I managed it to downscale it to a certain point I would like to discuss. The slicers become inactive but I still can use the slicers and the pivottable reacts on this. This is the expression.

    SumNetRevenue4:=IF(MAX(DimPeriod[PeriodID]) = 201605; [SumNetRevenue]; BLANK())


    Let me know what you think.


    Thanks.
    Friday, May 13, 2016 5:48 AM

Answers

  • It is hard to know for sure exactly what is going on since there isn't much detail about the model or measures, however by default slicers are inactive when the measure returns null.

    It looks like it is inactive because the test in your IF() statement is FALSE by default:

    MAX(DimPeriod[PeriodID]) = 201605

    Therefore, by default, your measure returns BLANK() which translates to null.  Hence, the inactive slicer.

    Again, this is just a guess based on very limited info.

    Here are some things you might check:

    Does the pivot have rows when no slicers are selected?

    Are there any additional filters/slicers on the pivot?

    Have you tried adding the slicer field as rows or columns in the pivot to see how each selection evaluates?

    Does your DimPeriod dimension contain dates that go beyond the dates in your Fact table?  If so, maybe you can reference the DateID from the Fact table instead of the period dimension or remove the future dates from the period dimension altogether?

    Also, you can toggle the inactive behavior of the slicer on or off in the slicer settings if it is working how you want aside from appearing inactive.


    • Proposed as answer by Charlie Liao Tuesday, May 24, 2016 10:27 AM
    • Marked as answer by Charlie Liao Monday, June 13, 2016 2:13 AM
    Thursday, May 19, 2016 8:42 PM
    Answerer

All replies

  • Anyone?
    Wednesday, May 18, 2016 7:23 AM
  • It is hard to know for sure exactly what is going on since there isn't much detail about the model or measures, however by default slicers are inactive when the measure returns null.

    It looks like it is inactive because the test in your IF() statement is FALSE by default:

    MAX(DimPeriod[PeriodID]) = 201605

    Therefore, by default, your measure returns BLANK() which translates to null.  Hence, the inactive slicer.

    Again, this is just a guess based on very limited info.

    Here are some things you might check:

    Does the pivot have rows when no slicers are selected?

    Are there any additional filters/slicers on the pivot?

    Have you tried adding the slicer field as rows or columns in the pivot to see how each selection evaluates?

    Does your DimPeriod dimension contain dates that go beyond the dates in your Fact table?  If so, maybe you can reference the DateID from the Fact table instead of the period dimension or remove the future dates from the period dimension altogether?

    Also, you can toggle the inactive behavior of the slicer on or off in the slicer settings if it is working how you want aside from appearing inactive.


    • Proposed as answer by Charlie Liao Tuesday, May 24, 2016 10:27 AM
    • Marked as answer by Charlie Liao Monday, June 13, 2016 2:13 AM
    Thursday, May 19, 2016 8:42 PM
    Answerer
  • I have found the workbook that I have used for testing the problem as described in my initial post. I could send you the workbook. 

    Now In my opinion the Measure calculates based on the MAX(DimPeriodMonth) (= filtercontext on PeriodMonthID) and there fore should always work. There is always a product involved and there for the slicer should be active?!?!


    Why are the slicers working even when  they are greyed out. It seems that they are wrongly greyed out.

    And this is the expression again:

    SumNetRevenue4:=IF(MAX(DimPeriod[PeriodID]) = 201605; [SumNetRevenue]; BLANK())

    • Edited by Hennie7863 Monday, August 15, 2016 11:30 AM
    Monday, August 15, 2016 11:29 AM