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.
I have 3 tables that have a relation.
I like to check if certain attributes are equal for all 3 tables. These attributes can be numbers, text, a combination or a boolean.
I expect one generic way as a solution, a kind of string comparison independed if its text or numbers.
Please advice
Solved! Go to Solution.
Hi @Zeur_Edwin
Edit relationship among these tables
Create measures in Sheet7
Compare1 = var com1=CALCULATE(DISTINCTCOUNT(Sheet7[at1]),FILTER(Sheet7,[at1]=MAX(Sheet8[at1]))) var com2=CALCULATE(DISTINCTCOUNT(Sheet7[at1]),FILTER(Sheet7,[at1]=MAX(Sheet9[at1]))) return IF(com1<>BLANK()&&com2<>BLANK(),"equal", "inequal")
Compare2 = var com1=CALCULATE(DISTINCTCOUNT(Sheet7[at2]),FILTER(Sheet7,[at2]=MAX(Sheet8[at2])))
var com2=CALCULATE(DISTINCTCOUNT(Sheet7[at2]),FILTER(Sheet7,[at2]=MAX(Sheet9[at2])))
return IF(com1<>BLANK()&&com2<>BLANK(),"equal", "inequal")
Compare3 = var com1=CALCULATE(DISTINCTCOUNT(Sheet7[at3]),FILTER(Sheet7,[at3]=MAX(Sheet8[at3])))
var com2=CALCULATE(DISTINCTCOUNT(Sheet7[at3]),FILTER(Sheet7,[at3]=MAX(Sheet9[at3])))
return IF(com1<>BLANK()&&com2<>BLANK(),"equal", "inequal")
Best Regards
Maggie
Ii concerns about 40k items with each ~30 attributes and that data can vary every day. So adding it all in one list would not be benificial.
Hi @Zeur_Edwin
Edit relationship among these tables
Create measures in Sheet7
Compare1 = var com1=CALCULATE(DISTINCTCOUNT(Sheet7[at1]),FILTER(Sheet7,[at1]=MAX(Sheet8[at1]))) var com2=CALCULATE(DISTINCTCOUNT(Sheet7[at1]),FILTER(Sheet7,[at1]=MAX(Sheet9[at1]))) return IF(com1<>BLANK()&&com2<>BLANK(),"equal", "inequal")
Compare2 = var com1=CALCULATE(DISTINCTCOUNT(Sheet7[at2]),FILTER(Sheet7,[at2]=MAX(Sheet8[at2])))
var com2=CALCULATE(DISTINCTCOUNT(Sheet7[at2]),FILTER(Sheet7,[at2]=MAX(Sheet9[at2])))
return IF(com1<>BLANK()&&com2<>BLANK(),"equal", "inequal")
Compare3 = var com1=CALCULATE(DISTINCTCOUNT(Sheet7[at3]),FILTER(Sheet7,[at3]=MAX(Sheet8[at3])))
var com2=CALCULATE(DISTINCTCOUNT(Sheet7[at3]),FILTER(Sheet7,[at3]=MAX(Sheet9[at3])))
return IF(com1<>BLANK()&&com2<>BLANK(),"equal", "inequal")
Best Regards
Maggie
An example fist the tables, second the wanted result.
@Zeur_Edwin Did you tried merging all these tables into single table (Joining by Identifier field) using "Merge Queries" option and then comparing all three fields against each other (which will be straight forward as all fields will be in single table after merging).
Proud to be a PBI Community Champion
@Zeur_Edwin Any sample data and expected output will be really helpful to provide any solution.
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |