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
joshuavd
Frequent Visitor

DAX Counting across two tables with multiple columns

I have two tables representing our foosball games played in office.  The first is what is generated by our software that has these columns:

Capture.PNG

 

A game can either be a single or doubles
 Capture1.PNGI

 

I have a second table that I want to identify if the game was a doubles or singles game.  I think of it as   if(Count of game = 2, "singles","doubles") but I can't figure out how to make this work.  Below is what I have done but it doesn't display 2s and 4s like I would expect...  Any help would be great, thanks!

 

Capture2.PNG

 

1 ACCEPTED SOLUTION

@joshuavd 

Type = IF ( COUNTROWS ( RELATEDTABLE('Foosey') ) = 2, "Singles", "Doubles")

SinglesOrDoubles.png

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

Type = 
//First get the number of players by counting the related rows in the Foosey table
Var Players = CALCULATE(COUNTROWS(Foosey),RELATEDTABLE(Games))
RETURN
  IF(Players = 2,"Singles","Doubles")

This assumes a relationship between Foosey and Games tables based upon Game columns. 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@joshuavd 

Type = IF ( COUNTROWS ( RELATEDTABLE('Foosey') ) = 2, "Singles", "Doubles")

SinglesOrDoubles.png

joshuavd
Frequent Visitor

Thanks!  I had tried this previously but it wasn't working, on further investigation I realized that I had not created a relationship between the tables... rookie mistake!  Really appreciate it!

@joshuavd - Glad you got it resolved, that's why I always try to call out assumed relationships when posting answers!


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.