Measure for counting that ignores only one filter context

[gitcen7]

The below is my data model.

'Competitor Count' in the below table is a measure. Formula: CALCULATE(DISTINCTCOUNT('Fact Table'[Competitor ID]),ALLSELECTED('Fact Table'[Competitor ID]))

The 'Competitor Count' column should be showing 9 instead of 1 for all rows in the table (since there are 9 competitors total for 'Variant ID' 10004). I've tried using ALLSELECTED on 'Competitor'[Competitor ID] also and that hasn't worked.

Clarification: The 'Competitor ID' column in the above visual is from the 'Competitors' dimension table, NOT the 'Fact Table'. The 'Variant ID' column in the above visual is from the 'Variants' dimension table, NOT the 'Fact Table'.

[sjoerdvn · Accepted solution]

You need the "ALL" on the dimension, otherwise it will keep filtering the fact. Adding the "IF" to prevent returning rows for Competitor/Variant combinations that do not exist.

[gitcen7]

This worked for me, thank you very much!

[Walter_W2022]

Hi @gitcen7 , I used your measure to count [Competitor ID] and got 9 for 10004, not sure why you got 1, could you please share your pbix file for further investigate? thanks.

[gitcen7]

Hi @Walter_W2022, to clarify, the Competitor ID column in the table is from the 'Competitors' data table, NOT the 'Fact Table'.

[Walter_W2022]

Hi @gitcen7  in this case, you only need to add REMOVEFILTERS, please see below
REMOVEFILTERS(DimCompetitorID[Competitor ID]

[gitcen6]

@Walter_W2022 - I've shared a pbix file since adding REMOVEFILTERS doesn't work either 

[gitcen6]

https://drive.google.com/file/d/12J5J1io3k-lvwgdf5X33AMGK-sFwol8l/view?usp=drive_link 

@Walter_W2022 - apologies for the delayed response. See the link for an example pbix file.