Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

DistinctCount with a complex measure

I have the following dax measure:    DistinctCount.GV.Contacted = CALCULATE( DISTINCTCOUNT( `table_1`[Operational_ID] ), FILTER( `table_1`, `table_1`[Completion_day] >= RELATED(`...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous ,

 

I found some possible solutions for your problem.

One is to use the HASONEVALUE function to check if the row context is a single value or not, and then return the distinct count or the sum of the distinct counts accordingly.

Another is to use the ALLSELECTED function to remove the filter context from the total calculation.

A third option is to use the ALLEXCEPT function to preserve only the columns that you want to group by.

A forth option is to use SUMMARIZE to make a virtual table and sum the distinctcountings up.

You can check the links for more details and examples. I hope this helps!

 

powerbi - Power BI Distinct Count Incorrect Total - Stack Overflow

SUMMARIZE:

Solved: COUNT DISTINCT SHOWS WRONG GRAND TOTAL - Microsoft Fabric Community

ALLEXCEPT:

Solved: DAX help group by distinct count measure - Microsoft Fabric Community

 

 

Best Regards,

Stephen Tao

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous, 

    Thank you for you answer, I found a workaround with this: 

    SUMX(VALUES(TABLE[TABLE_ID]), [MEASURE])

     So, I had to create another layer of measures, the disadvantage of this: it hits the performance, so I'll try your suggestions as well, many thanks!