Measure to show dynamic values based on another a related Table

[Venkateshwaran]

Case:

I have a following Tables

Data Table

ID	Customer ID	Location Id	Product Id	PnL
100	C01	L01	P01	1000
101	C02	L01	P02	2000
103	C03	L02	P01	3000

Customer Table

ID	Customer Name
C01	Cust 01
C02	Cust 02
C03	Cust 03

Location Table

ID	Location Name
L01	Loc 01
C02	Loc 02

Product Table

ID	Product Name
P01	Prod 01
P02	Prod 02

Group By Table

GroupBy

Customer

Location

Product

The Data table is linked to the master Table through Ids. The Group Table is not linked to any tables

Question:

Is there a way to write a measure in the "Data Table" to show either "Customer Table [Customer Name]" or "Location Table [Location Name]" or "Product Table [Product Name]" depending on what I select in "GroupBy" Table.

Any help is much appriciated. 

[v-yuta-msft · Accepted solution]

@Venkateshwaran,

To achieve this, you should build relationship between Data and Customer, Location, Product, currently there's no basic column which can build the relationship in given table so I have created additional index column in the 4 tables. The relationship is like diagram below:

  

Then create a measure using DAX formula like pattern below:

Measure = 
SWITCH (
    SELECTEDVALUE ( 'Group By Table'[GroupBy] ),
    "Customer", MAXX ( RELATEDTABLE ( Customer ), Customer[Customer Name] ),
    "Location", MAXX ( RELATEDTABLE ( Location ), Location[Location Name] ),
    "Product", MAXX ( RELATEDTABLE ( 'Product' ), 'Product'[Product Name] )
)

 

You can also refer to the appendix.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PattemManohar]

@Venkateshwaran Please try using "SELECTEDVALUE" funtion. It will be helpful to suggest an accurate solution, if you can provide your expected output as well.