locked
Slicers not hiding values for multiple pivots of the same ThisWorkbookModel for certain measures RRS feed

  • Question

  • I have a Facts table and multiple single-column dimension tables connected to fact table by 1 to many relationship. i have created multiple slicers from the dimension tables to filter the data in the facts table. I have clicked Slicer settings to "Hide items with no data". I have created 2 pivots from the ThisWorkbookModel and added 2 measures to the pivots.

    Count of Prev Char Value:= CALCULATE([Count of Char Value],SAMEPERIODLASTYEAR(dDate[Date]))

    Here Char Value is from the fData facts table that i want filtered, based on the dDate[Date] from the dDate dimension table for the same period last year. I cannot use the date column from the facts table as the dates are not complete or in sequence.

    %Change:= IF(IFERROR([Count of Char Value]/[Count of Prev Char Value]-1,BLANK()),IF(([Count of Char Value]/[Count of Prev Char Value]-1)=0,BLANK(),[Count of Char Value]/[Count of Prev Char Value]-1))

    If i use just the [Count of Char Value] or [Count of Prev Char Value], the Slicers hide the items with no data.

    Dashboard with filtered items

    PT1 with Count of Char ValuePT2 with Count of Char Value & Count of Prev Char Value

    But if i use [% Change] in one or both of the Pivots, the Slicers do not hide the items with no data.

    Dashboard now showing unfiltered slicers

    PT1 with %ChangePT2 with %Change

    I tried adding Year to one of the Pivots, for the SamePeriodLastYear function to work. Also, i suspect the [Count of Prev Value] and the [%Change] measure are also not working properly. Why is this happening and how can i resolve it?


    Regards. ~~SiFaR~~


    • Edited by sifar786 Wednesday, December 12, 2018 11:11 AM
    Wednesday, December 12, 2018 10:57 AM

All replies