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 took below data into power bi and it has come many to many relationships on unique id or ch3 status.
when i tried to taking the column from 2017 table to 2018 to compare any change in ch3 status to last year and this year using related function it is not allowing me to do.
Can someone help me on comparison of change in 2017 to 2018 ch3 status for both the years.
Solved! Go to Solution.
Hi @Anonymous ,
In both 2017 and 2018 tables, for each RCP ACCOUNT NO, there existing multiple records which share the same CH3 STATUS value, right?
If so, you could create a bridge table which lists all available unique Account.
Dim account = UNION ( VALUES ( '2017 data'[Account] ), EXCEPT ( VALUES ( '2018 data'[Account] ), VALUES ( '2017 data'[Account] ) ) )
Create a one to many relationship from Dim account table to 2017 and 2018 data table based on RCP ACCOUONT NO.
Add relative fields into table visual, you could rename each field being displayed in column header.
Best regards,
Yuliana Gu
Hi @Anonymous ,
You need to establish a one to one relationship between 2017 table and 2018 table based on UniqueId field.
Create a calculated column in 2018 table.
LY status = RELATED('2017 data'[Ch3 status])
Best regards,
Yuliana Gu
Hi @v-yulgu-msft ,
There are duplicates in Unique ID as well which i can't share all the data file here.
when i create a relationship on Unique ID it is coming as MANY TO MANY .... and i guess you know Related dax function doesn't work on Many to many relationship.
Now could you please suggest me how to get a column.
Regards,
Karthik S
Hi @Anonymous ,
Since the reationship type is many to many between two tables, how should we determine which record to be returned from 2017 table to 2018 table? For example, there existing multiple records for Id 10001 in both tables, how to match them?
Please post some dummy data that can reproduce your scenario and show us your desired output. How to Get Your Question Answered Quickly
Regards,
Yuliana Gu
Please find below the link, you can find the data in the link provided.
https://drive.google.com/file/d/1Q9GnkVJJAmt20djYC8YDBIvxeBEfJr-h/view?usp=sharing
Also, i need to get the output as below where you can ignore recipient code and take only RCP ACCOUNT NO AND CH3 STATUS.
Please do let me know if you need anyother info.
Hi @Anonymous ,
In both 2017 and 2018 tables, for each RCP ACCOUNT NO, there existing multiple records which share the same CH3 STATUS value, right?
If so, you could create a bridge table which lists all available unique Account.
Dim account = UNION ( VALUES ( '2017 data'[Account] ), EXCEPT ( VALUES ( '2018 data'[Account] ), VALUES ( '2017 data'[Account] ) ) )
Create a one to many relationship from Dim account table to 2017 and 2018 data table based on RCP ACCOUONT NO.
Add relative fields into table visual, you could rename each field being displayed in column header.
Best regards,
Yuliana Gu
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 |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |