Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Zeur_Edwin
New Member

Comparing Cell content, strings, from 3 different tables

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

1 ACCEPTED SOLUTION

Hi @Zeur_Edwin

Edit relationship among these tables

7.png

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")

8.png

 

Best Regards

Maggie

View solution in original post

5 REPLIES 5
Zeur_Edwin
New Member

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

7.png

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")

8.png

 

Best Regards

Maggie

Zeur_Edwin
New Member

An example fist the tables, second the wanted result.

 

 

tables.jpganalysis.jpg

@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). 





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




PattemManohar
Community Champion
Community Champion

@Zeur_Edwin Any sample data and expected output will be really helpful to provide any solution.





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.