Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |