Distinct count for events generated by the same customers

DAX DAX Calculations
1

Hello

I have a (heavily redacted) table containing events since 1st January 2016.
The table presents the customers in regions, location types and locations. Each customer can generate two types of events (serious and reportable) on three different categories (accident, incident and behaviour)

The file is attached:
MockUp_v2.xlsx (1.8 MB)

A simple DISTINCTCOUNT() does the trick beautifully, at the total level. However, when I introduce some filter context, the breakdown goes horribly wrong.

I am aware of the warning in the documentation (Microsoft Doc, but I needed it translated into English…
The reason I don’t get this explanation is that a much smaller example works perfectly well:
MockUp.xlsx (121.6 KB)

I’ve spent about two days trying to understand this, but in vain.
Any help will be appreciated.

Regards

Doru

2

What is the measure you are trying to use with DISTINCTCOUNT that isn’t working for you? Also, if you have a PBIX file to share that is very helpful when trying to answer questions here in the forum.

Thanks

Enterprise%20DNA%20Expert%20-%20Small

3

I cannot attach the PowerBI file, due to privacy concerns.

The file I attached (MockUp_v2.xlsx) contains two tables in the Data Model. The measure I run is called “Customer Events” and resides in the Events table.

Customer Events:= DISTINCTCOUNT( Events[Customer] )

It could have been:
Customer Events:=COUNTROWS(
     DISTINCT( Events[Customer] )
)

Please note that wrapping Customer Events in a CALCULATE() function does not change anything.

4

If you want to introduce filter context, then you would need to use CALCULATE. Here is a sample measure that uses CALCULATE.

CALCULATE (
DISTINCTCOUNT ( Events[Customer] ),
FILTER(Events,Events[Type] = "Reportable"
))

Let me know if this is what you were thinking, or what you are trying to accomplish.

Thanks

Enterprise%20DNA%20Expert%20-%20Small

5

@dimbroane, May I ask what do you want “Customer Events” to measure? From what I see in the Pivot Tables the correct formula is being used to report the number of (Unique) customers within the Region/Category or Type. It’s only when the table is segmented to the customer level that the measure becomes less meaningful ie. Customer Events = 1.