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.
