Show only one value for same Id else leave it blank

[ItSupportElm]

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.

[DataInsights · Accepted solution]

@ItSupportElm,

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.