Forum Discussion
DistinctCount with different outputs
Hi DAX experts,
I have a bit of a mystery. I get two different outputs using the same DAX formula in two different tables.
The Response ID the same data type used in two different tables.
DF_DIM has the data in unique, long format.
Survey_FACT has the data in many, long format.
Yet I get two competing outputs. Survey_Fact has the correct output. Would appreciate a nudge in troubleshooting why DF_DIM does not produce the correct output.
Total DF =
DISTINCTCOUNT(DF_Dim[Response ID])
Total Responses = DISTINCTCOUNT('Survey Fact'[Response ID])
Here is the link to the dashboard and data. The Personal Information has been scrubbed. The response ID is an encrypted code.
Correct ouput
Incorrect Frequency
Sincerely,
Luis Pablo Martinez
2 Replies
Hi Anonymous ,
You have guided me to the answer.
I created this measure that works well.
Total DF =CALCULATE([Total Responses],CROSSFILTER(DF_Dim[DF_ID], 'Survey Fact'[DF_ID], Both))I read in Expert Modeling in Power BI by Soheil Bahkhshi and Definitive Guide to DAX by SQLBI to rarely use the bidirectional filter.Instead to use the crossfilter() to avoid ambiguity.Your response got me there.Thank you.