Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am strugling with the DAX formula (new to DAX..) for counting how many rows in one table match the values in another one. Here the example:
Table 1 = ID, quantity
Table 2 = ID, quantity
I need to sum the quantity of ID in Table 2 being present in Table 1. Any suggestion on this?
Thanks, Alex
Guys I tried all proposed solution but none worked for me.
I tried to literally copy yours approach but none worked. I think the issue is that I have a many to many relationship (with a table in between). I perhaps didn't give a detail that makes the formula more difficult.
Table 1: actual hours worked on a given taskID in a given day
Table 2: planned hours on a given taskID in a given day
Table 3: list of all TaskID with TaskName
Table 1: RegistrationDate, ID, Quantity
Table 2: PlanningDate, ID, Quantity
Table 3: ID, IDName
In fact, I need to:
- count how many IDs we actually worked that were planned
- sum the hours actually worked in the IDs that were planned.
Hi, @allejot
We don't recommend you relate many-to-many dimension-type tables directly. Please refer to the following guidance:
For further information, you may refer to the document .
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @allejot
Based on your desscription, I created data to reproduce your scenario. The pbix file is attached in the end.
Table1:
Table2:
There is no relationship between two tables. You may create calculated columns or measures as below.
Calculated column:
Count Column =
COUNTROWS(
FILTER(
Table2,
Table2[ID]=EARLIER(Table1[ID])
)
)
Sum Column =
CALCULATE(
SUM(Table2[Quantity]),
FILTER(
Table2,
Table2[ID]=EARLIER(Table1[ID])
)
)
Measure:
Count Measure =
COUNTROWS(
FILTER(
Table2,
Table2[ID]=SELECTEDVALUE(Table1[ID])
)
)
Sum Measure =
CALCULATE(
SUM(Table2[Quantity]),
FILTER(
Table2,
Table2[ID]=SELECTEDVALUE(Table1[ID])
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@allejot , Create a new column in Table 2
new column =countx(filter(table1,table1[ID] =table2[ID]),table[ID])
any value means that is present in table 1
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |