HELP! DAX logic - cant figure out.

[Anonymous]

I have a table designed vertically for dimensions.

Product Table - 

Product Dimension	Product Category
A	Fruit
B	Vegetable
C	Diary

SomePrimaryID	Product Dimension	Value
1	A	1
2	A	2
3	B	3
1	B	4
2	B	5
1	C	6
3	C	5

Now, I have a slicer on Product Dimension.

A

B

C

When user selects A and C together, I want magical DAX to do the following -

For A, the following data belongs to Product A as per table above.

SomePrimaryID	Product Dimension	Value
1	A	1
2	A	2

For C, the following data belongs to Product C as per table above.

SomePrimaryID	Product Dimension	Value
1	C	6
3	C	5

Now, I want only unique primary IDs to be considered between A and C to get SUM(Value).

Looking at above - unique primary ID between A and C is 1.

SomePrimaryID	Product Dimension	Value
1	A	1
1	C	6

Therefore, Product Dimension A & C selected in the slicer should return the following in -

Product Dimension	Value
A	7
Total	7

DAX I tried: 

VAR ProdFruits =

CalculateTable (Values(SomePrimaryID), Filter(Products, Product = "Fruit")

Var ProdDiary =

CalculateTable (Values(SomePrimaryID), Filter(Products, Product = "Diary")

Var Intersection =

NATURALINNERJOIN(ProdFruits, ProdDiary)

RETURN

CALCULATE (SUMX(Intersection, SUM(Value)), Product Dimension = "Fruit")

For some reason, I am not able to figure out where am i going wrong and why its not working!!

I hope I was clear enough in explaining the problem examples above. Thanks in advance.

Link to sample PBIX file - SAMPLE PBIX FILE 

[camargos88]

Hi @Anonymous ,

Try this code:

CalculatedValue =

VAR _min = CALCULATE(MIN(ProductFactTable[SomePrimaryID]); ALLEXCEPT(ProductFactTable; ProductFactTable[Product Dimension]))

RETURN CALCULATE(SUM(ProductFactTable[Value]); FILTER(ALLEXCEPT(ProductFactTable; Products[Product Dimension]); ProductFactTable[SomePrimaryID] = _min))

Ricardo

[Anonymous · Accepted solution]

Thanks Ricardo, I tried the DAX above and doesnt seem to give me desired output :(.

When I replaced the measure with your suggested DAX, I get the following -

Product Dimension	CalculatedValue
A	1
C	6
Total	7

However, what i want to see is the following -

Product Dimension	CalculatedValue
A	7
C	7
Total	7

I believe it is very close to the solution, not sure what other manipulations in DAX syntax can be done to acheive above?

Your help is much appreciated.

Thanks.

[Anonymous]

Find the solution attached. But note that measures where ALLSELECTED is used should never be used in ITERATORS. This is of utmost importance. If you ignore this rule, your measures will be WRONG. Simply. And you won't be able to understand why. You'll need to read a lot about how ALLSELECTED works and there's a lot to understand.

Best

D