Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
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
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
Yes you can
Count =
COUNTROWS (
FILTER (
Participation,
Participation[Category] = "Co-curirrcular"
&& Participation[Preferred] = Sudent[Preferred]
)
) + 0
Two questions: do you have relationship between the two tables? Are you creating a calculated coumn in the student table?
Hi @pickslides
please try
Count =
COUNTROWS (
FILTER (
Participation,
Participation[Category] = "Co-curirrcular"
&& Participation[Preferred] = Sudent[Preferred]
)
)
This didnt work but it didnt give me an error. Thanks!
Yes, I have a one to many relationship on the UID field
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
9 |
User | Count |
---|---|
28 | |
23 | |
12 | |
11 | |
10 |