topic Looking up values across different tables in DAX Commands and Tips

Looking up values across different tables

pickslides — Sun, 26 Jun 2022 09:14:38 GMT

Hi, I have two tables. One is a list of students the other a list of activities. I wish to count (in DAX) the number of distinct activities they have choosen if they are "Co-curirrcular" (Green). I have the output column in yellow and red values.

Thanks Q

Re: Looking up values across different tables

tamerj1 — Sun, 26 Jun 2022 09:24:29 GMT

Hi pickslides

please try

Count = COUNTROWS ( FILTER ( Participation, Participation[Category] = "Co-curirrcular" && Participation[Preferred] = Sudent[Preferred] ) )

Re: Looking up values across different tables

pickslides — Sun, 26 Jun 2022 09:53:38 GMT

This didnt work but it didnt give me an error. Thanks!

Re: Looking up values across different tables

tamerj1 — Sun, 26 Jun 2022 10:00:16 GMT

pickslides

Do you have any relationship between the two tables?

Re: Looking up values across different tables

pickslides — Sun, 26 Jun 2022 10:02:56 GMT

Yes, I have a one to many relationship on the UID field

Re: Looking up values across different tables

pickslides — Sun, 26 Jun 2022 10:06:59 GMT

A-ha.. I found a typo in the formula. Seems to work now.

If the result is zero, cells are blank, can I populate with 0 somehow?

Re: Looking up values across different tables

tamerj1 — Sun, 26 Jun 2022 10:12:34 GMT

pickslides

Two questions: do you have relationship between the two tables? Are you creating a calculated coumn in the student table?

Re: Looking up values across different tables

tamerj1 — Sun, 26 Jun 2022 10:14:43 GMT

pickslides

Yes you can

Count = COUNTROWS ( FILTER ( Participation, Participation[Category] = "Co-curirrcular" && Participation[Preferred] = Sudent[Preferred] ) ) + 0

Re: Looking up values across different tables

Anonymous — Wed, 29 Jun 2022 07:50:26 GMT

Hi pickslides ,

Here are the steps you can follow：

1. Create calculated column.

TABLE Participation:

Flag1 = var _flag1=IF( 'TABLE Participation'[Category]="Co-Curricular Activities",1,0) return SUMX( FILTER(ALL('TABLE Participation'), 'TABLE Participation'[Perferred]=EARLIER('TABLE Participation'[Perferred]) &&'TABLE Participation'[Category]=EARLIER('TABLE Participation'[Category]) ),_flag1)

Table Student:

Count = var _if1= IF( NOT( ISBLANK( CALCULATE( MAX('TABLE Participation'[Perferred]),FILTER(ALL('TABLE Participation'), 'TABLE Participation'[UID]='Table Student'[UID])))), CALCULATE(MAX('TABLE Participation'[Flag1]),FILTER(all('TABLE Participation'),'TABLE Participation'[UID]=EARLIER('Table Student'[UID])))) return IF( _if1 =0,BLANK(),_if1)

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly