powerbidaxpowerquerypowerbi-desktopstar-schema

Count Distinct Values in one column table related to another column table Power BI DAX


I want to visualize in a power bi chart the distinct count values from one column referencing another table column. This should be done using a measure, not a calculated column. So I have a fact table like this one:

user_id application_id client_id date_id
1 3 2 1
1 1 1 1
2 3 2 2
3 1 2 2
4 2 1 3
5 2 1 4
5 4 3 3
5 4 3 3

Here is the user dimension table:

user_id user_code name status date_change
1 1 Johann 1 07-23-2021
2 1 Johann 0 07-30-2021
3 1 Johann 1 10-17-2021
4 2 Ana 0 12-14-2021
5 2 Ana 1 01-22-2022
6 4 Nick 1 12-23-2021

As you can see in DimUser, user_code identifies a user uniquely, not user_id, but relatioinship is made to Fact table using user_id column. What I want to figure out is how to count the number of users by client in Fact table using the user_id column to access user_code values in DimUser table. I could create a calculated column in fact table retrieving the user_code related to the user_id and create a measure by simply using DISTINCTCOUNT function, but as I mentioned earlier, I want to avoid creation of calculated columns, this should be done using solely measures.

In the end I should visualize a chart table with the following information:

client id client name Number of users
1 Ecosapiens 2
2 Jalisco 1
3 Greenplant 1

Because user_id values 1,2 and 3 are the same user (user_code = 1) and user_id values 4 and 5 are another user (user_code = 2), client_id 1 has 2 users, client_id 2 has just one user and client_id 3 has 1 user too.

How is the DAX code to create that measure?

I'd really appretiate if you could help me.


Solution

  • Just add the following measure.

    Number of Users = CALCULATE( DISTINCTCOUNT('dimension'[user_code]), CROSSFILTER('fact'[user_id],'dimension'[user_id], Both))
    

    enter image description here

    enter image description here