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.
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.
Solved! Go to Solution.
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.
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.
@Venkateshwaran Please try using "SELECTEDVALUE" funtion. It will be helpful to suggest an accurate solution, if you can provide your expected output as well.
Proud to be a PBI Community Champion
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |