Forum Discussion

WendyWang303's avatar
WendyWang303
Frequent Visitor
1 year ago
Solved

How to remove slicer filter when Sumx distinct count

 

I have a measure that needs to show both the distinct count and the sum of distinct counts on the total line.

I can create this measure in different ways, and both approaches work.

However, we also need to remove one slicer filter for this measure. I'm not sure how to add the code to remove the slicer filter, Like under screenshot, when Slicer show "All", measure total show 92, when slicer pick one value, it will show 44,

we always need the total to show 92, regardless of the Ticket Type selected in the slicer. 

share both measure here, if help me modify code remove slicer filter, 

Thank you for your help.

Measure1 =

VAR S =

    SUMMARIZE (

        'CX_Scorecard',

        CX_Scorecard[Warranty_Community_No],

        "LotDistinct", DISTINCTCOUNT ( CX_Scorecard[Lot_Number] ) )

RETURN

    SUMX ( S, [LotDistinct] )

 

 

 

Measure2 =

COUNTROWS (

    DISTINCT (

        SELECTCOLUMNS (

          'CX_Scorecard',

           "LotDistinct",  'CX_Scorecard'[Lot_Number])))

 

 

 

Need Help
  • WendyWang303's avatar
    WendyWang303
    1 year ago

    Good suggestion, I add new table and create relationship, but still not works 

     

8 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    1 year ago

    I think you might need to remove the filters on ticket type both when you are generating the initial list of warranties and when counting the number of lots. Try

    Num distinct lots =
SUMX (
    CALCULATETABLE (
        VALUES ( CX_Scorecard[Warranty_Community_No] ),
        REMOVEFILTERS ( 'Table'[Ticket Type] )
    ),
    CALCULATE (
        DISTINCTCOUNT ( CX_Scorecard[Lot_Number] ),
        REMOVEFILTERS ( 'Table'[Ticket Type] )
    )
)

    where 'Table'[Ticket Type] is the column used in the slicer.

    If that column is sorted by a different column then you would need to also add the sort by column into the REMOVEFILTERS.

    • WendyWang303's avatar
      WendyWang303
      Frequent Visitor
      1 year ago

      Thank you so much for your quick help, I just tried, it still show same result : 

       

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        1 year ago

        Are all the columns on the same table ? If so this might be a case where auto exists is causing a problem.

        If they are all on the same table, create a separate table like

        Ticket Type =
DISTINCT ( CX_Scorecard[Ticket Type] )

        and create a one-to-many relationship from the new table to CX_Scorecard. Use the new table in your slicer, and any other visuals, and change the measure code to REMOVEFILTERS from the new table rather than CX_Scorecard.

    • WendyWang303's avatar
      WendyWang303
      Frequent Visitor
      1 year ago

      Thank you for your suggestion, they are all from same table, looks this code not works, still show 44

       

  • sjoerdvn's avatar
    sjoerdvn
    Icon for Solution Sage rankSolution Sage
    1 year ago

    You haven't shared information about the source of that slicer, so for now I assume it is on CX_Scorecard'[Ticket Type]. If it's not, adjust the code below. Also, Measure2 is functionally the same as DISTINCTCOUNT( CX_Scorecard[Lot_Number] )

    Measure1 =
VAR S =
    SUMMARIZE (
        CALCULATE('CX_Scorecard',ALL(CX_Scorecard[Ticket Type]))
        CX_Scorecard[Warranty_Community_No],
        "LotDistinct", DISTINCTCOUNT( CX_Scorecard[Lot_Number] ) )
RETURN
    SUMX ( S, [LotDistinct] )

Measure2 =
CALCULATE(DISTINCTCOUNT( CX_Scorecard[Lot_Number],ALL(CX_Scorecard[Ticket Type]) )