Forum Discussion
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(`table_2`[Status_day])
),
FILTER(
`table_2`,
`table_2`[Agent_contacted_ID] = RELATED(`table_3`[TASK_USR_ID])
)
)
As usual, my problem is that if I sum up the volumes row by row it won't match with the matrix table's total, I tried to check other solutions mentioned by the community, but those did not work for me. Can you please help?
2 Replies
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.