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.
Hi All,
I have flat input data which has APPID(MANY) to join with other table having APPID (ONE) so the relation wil be ONE TO MANY kind.
Here i need to create a new table in power BI desktop for the flat input data in a format that could make ONE TO ONE realtionship with other table before building any visualization.
This new format i need to develop in query editor or Data but not using any visual in report tab.
Attaching the input and required output screenshots
Hi pabburi,
From your description, it looks like you already created a 1:N relationship, may I know is there any specific purpose to build a 1:1 relationship instead?
Based on my research, it’s not able to implement a table OUTPUT showing in your picture via query editor or Data view, to work around the issue, you can follow below steps:
Firstly, create new Intermediate table named A with unique APP-ID value, using DAX below:
A = DISTINCT(INPUT[APP-ID])
Then, create new relationship between table INPUT and table A ,setting Cardinality as "Many to one" and Cross filter direction as "Both". This means for filtering purposes, both tables are treated as if they're a single table. Finally ,you can make One to one relationship using table A with other table before building any visualization.
Attachment is my pbix test file link: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EVU1Z7KcmzxBq9gyU7...
If you have any other issue, please feel free to ask.
Best Regards,
Amy
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 |
---|---|
118 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
148 | |
104 | |
104 | |
89 | |
66 |