Help with relationships

[Tim1984]

Hi All,

Still early learning this, however I am having issues with relationships

What I am trying to work out is how to show the description for multi columns ( only seem to be getting one) 

table 1 - Client

table 2 - master 

Client Table

Name	Gender	Description
John Smith	0001	0004
Claire	0002	Null
Bob	0001	0003

Master Table

ID	Description
0001	Male
0002	Female
0003	Australia
0004	New Zealand

Power BI dash ( how i want it to show) 

Client	Gender	Country of birth
John smith	Male	New Zealand
Claire	Female
Bob	Male	Australia

Currently it will show the gender but not the country of birth ( as just shows the ID not the Desciption 

I currently have a one to many relationship setup for Master table (ID) to Client table (Gender)

help please as I have about 15 fields that have IDs that all live in the Master table with a description.

[Irwan · Accepted solution]

hello @Tim1984 

you have unique ID in Master table and you are fine with measure, then you can do this without building relationship between those two table.

1. create Gender measure with following DAX

Gender Measure = 
var _ID = SELECTEDVALUE('Table 1'[Gender])
Return
MAXX(
    FILTER(
        'Table 2',
        'Table 2'[ID]=_ID
    ),
    'Table 2'[Description]
)

2. create Country of Birth measure with following DAX.

Country of Birth = 
var _ID = SELECTEDVALUE('Table 1'[Description])
Return
MAXX(
    FILTER(
        'Table 2',
        'Table 2'[ID]=_ID
    ),
    'Table 2'[Description]
)

basically, the idea is finding the same ID between those two table then return the result in a column you want.

Hope this will help.

Thank you.

[v-zhouwen-msft]

Hi all,thanks for the quick reply, I'll add more.

Hi @Tim1984 ,

Use the following DAX expression to create a measure

Measure = 
VAR _description = SELECTEDVALUE(Client[Description])
RETURN LOOKUPVALUE('Master'[Description],Master[ID],_description)

Turn on the 'Show item with no data' option

Best Regards,
Wenbin Zhou

[Jihwan_Kim · Accepted solution]

Hi,

One of ways to achieve this is to use USERELATIONSHIP DAX function in measures.

Please check the below picture and the attached pbix file.

USERELATIONSHIP function (DAX) - DAX | Microsoft Learn

Gender: = 
IF (
    HASONEVALUE ( Client[Name] ),
    MAXX ( SUMMARIZE ( Client, Master[Description] ), Master[Description] )
)

Country of birth: = 
CALCULATE ( [Gender:], USERELATIONSHIP ( Master[ID], Client[Description] ) )

[Irwan · Accepted solution]

hello @Tim1984 

you have unique ID in Master table and you are fine with measure, then you can do this without building relationship between those two table.

1. create Gender measure with following DAX

Gender Measure = 
var _ID = SELECTEDVALUE('Table 1'[Gender])
Return
MAXX(
    FILTER(
        'Table 2',
        'Table 2'[ID]=_ID
    ),
    'Table 2'[Description]
)

2. create Country of Birth measure with following DAX.

Country of Birth = 
var _ID = SELECTEDVALUE('Table 1'[Description])
Return
MAXX(
    FILTER(
        'Table 2',
        'Table 2'[ID]=_ID
    ),
    'Table 2'[Description]
)

basically, the idea is finding the same ID between those two table then return the result in a column you want.

Hope this will help.

Thank you.