Forum Discussion
Helper IIIShow only one value for same Id else leave it blank
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.
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 vResultResult:
Your example shows Blank for IdUser2, but this user has role "AA". This is why my result is slightly different from yours.
1 Reply
- DataInsights
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 vResultResult:
Your example shows Blank for IdUser2, but this user has role "AA". This is why my result is slightly different from yours.