topic Re: Measure for counting that ignores only one filter context in DAX Commands and Tips

Measure for counting that ignores only one filter context

gitcen7 — Mon, 11 Mar 2024 15:04:38 GMT

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'.

Re: Measure for counting that ignores only one filter context

Walter_W2022 — Mon, 11 Mar 2024 06:29:44 GMT

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.

Re: Measure for counting that ignores only one filter context

gitcen7 — Mon, 11 Mar 2024 14:25:19 GMT

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

Re: Measure for counting that ignores only one filter context

Walter_W2022 — Mon, 11 Mar 2024 21:33:45 GMT

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

Re: Measure for counting that ignores only one filter context

gitcen6 — Mon, 18 Mar 2024 04:40:52 GMT

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.

Re: Measure for counting that ignores only one filter context

gitcen6 — Mon, 18 Mar 2024 14:09:30 GMT

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

Re: Measure for counting that ignores only one filter context

sjoerdvn — Mon, 18 Mar 2024 15:10:49 GMT

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.

Re: Measure for counting that ignores only one filter context

gitcen7 — Sun, 07 Apr 2024 21:38:47 GMT

This worked for me, thank you very much!