DistinctCount with different outputs

[Anonymous]

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. 

Winter Survey

Correct ouput

Incorrect Frequency

Sincerely,

Luis Pablo Martinez

[Anonymous]

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.

[Anonymous · Accepted solution]

Hi @Anonymous 

In Model view, change the cross filter direction from single to both

This is the result you want

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.