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_idapplication_idclient_iddate_id13211111232231224213521454335433

Here is the user dimension table:

user_iduser_codenamestatusdate_change11Johann107-23-202121Johann007-30-202131Johann110-17-202142Ana012-14-202152Ana101-22-202264Nick112-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 idclient nameNumber of users1Ecosapiens22Jalisco13Greenplant1

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.

1

Best Answer


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