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.
Here is my scenario (simplified):
I have a reference table: TableA
ID | description |
A | this is item A |
B | this is Item B |
C | this is Item C |
And a detail table: TableB
Item ID | Category | Quantity |
A | Bikes | 1 |
A | Bikes | 1 |
A | Cars | 1 |
B | Bikes | 1 |
B | Cars | 1 |
C | Cars | 1 |
C | Cars | 1 |
There is a one:many relationship between TableA->TableB on the item id.
Here is what I want:
I tried the following measure, and then adding description but it seems like calculate (or allexcept) is messing up filter context when I try to drag in "description" from TableA.
Solved! Go to Solution.
Hi @Anonymous
You can get your measure to work as well. You just need a few filters on the category. They do it in a slightly different way but you can get to what you want.
you could try this
Bikes Quantity 1 = CALCULATE(SUM(TableB[Quantity]), filter(ALL(TableB),TableB[Item ID]=max(TableB[Item ID]) && TableB[Category]="Bikes"))
But you would need to include the Quantity column for it to show and it would be blank rather than 0 which isn't ideal.
you could do it with a slightly different formula
Bikes Quantity 1 = if(ISBLANK( CALCULATE(SUM(TableB[Quantity]), filter(ALL(TableB),TableB[Item ID]=max(TableB[Item ID]) && TableB[Category]="Bikes"))),0, CALCULATE(SUM(TableB[Quantity]), filter(ALL(TableB),TableB[Item ID]=max(TableB[Item ID]) && TableB[Category]="Bikes")))
Set category to be "First"
@gooranga1Thanks for the query, seems to be working! Any chance you could go into some detail as to how this is working (what's actually happening?)
Hi @Anonymous
You can get your measure to work as well. You just need a few filters on the category. They do it in a slightly different way but you can get to what you want.
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |