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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Looking up values across different tables

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

8 REPLIES 8
Community Support

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

Helper I

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?

Q

Super User

Yes you can

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

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

Super User

please try

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

Helper I

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

Super User

Do you have any relationship between the two tables?

Helper I

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

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors