Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have an issue on my report and i dont know how to do it,
i have this table :
IdRole | NameRole |
1 | AA |
2 | AB |
3 | AC |
And this second table :
IdUser | IdRole |
1 | 1 |
1 | 2 |
2 | 1 |
3 | 3 |
And i have my last table for example with somes informations :
IdUser | Date | Point | NameRole |
1 | 03/05/2021 | 500 | AA |
1 | 01/05/2021 | 500 | AA |
2 | 03/03/2020 | 800 | Blank |
3 | 17/02/2020 | 1000 | Blank |
I want to have this last colum as a result, The User have many role but the only Role that im interested is to know if he have "AA" role else leave it blank.
Im in DirectQuery if someone can help me please.
Thank you for the futur answer.
Solved! Go to Solution.
Try this solution.
1. Create a Users table (one row per user).
2. Create a data model as shown below.
3. Create a calculated column in table UserPoints:
NameRole =
VAR vRowCount =
CALCULATE ( COUNTROWS ( UserRoles ), Roles[NameRole] = "AA" )
VAR vResult =
IF ( vRowCount > 0, "AA" )
RETURN
vResult
Result:
Your example shows Blank for IdUser2, but this user has role "AA". This is why my result is slightly different from yours.
Proud to be a Super User!
Try this solution.
1. Create a Users table (one row per user).
2. Create a data model as shown below.
3. Create a calculated column in table UserPoints:
NameRole =
VAR vRowCount =
CALCULATE ( COUNTROWS ( UserRoles ), Roles[NameRole] = "AA" )
VAR vResult =
IF ( vRowCount > 0, "AA" )
RETURN
vResult
Result:
Your example shows Blank for IdUser2, but this user has role "AA". This is why my result is slightly different from yours.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |