How to remove slicer filter when Sumx distinct count

[WendyWang303]

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])))

[sjoerdvn]

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]) )

[johnt75]

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]

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

[WendyWang303]

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

[johnt75]

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]

Thank you very much, this measure works after I remove other filter on report level. 

[WendyWang303 · Accepted solution]

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

[johnt75]

Is it possible to share a PBIX with any confidential info removed ? You could post a link to Google Drive, OneDrive etc.