Dax multiple conditional sum

[shei7141]

Hi guys,

I have a table which looks like this:

Store	Phases	Estimated cost
J&J	A	1
J&J	B
J&J	C	2
J&J	D	3
TimLee	A	5
TimLee	B	3
TimLee	C
TimLee	D	1

In all the cases, if estimatedCost of B is blank C will have some estimated cost or if B has some cost the C will be blank

I have slicer where user can select any store name

I want to create a MEASURE which can do a conditional sum based on the following conditions:

1: if Phase B's estimated cost is blank measure = 1 otherwise measure = 2

Any pointers will be greatly appreciated 🙂

Ta

Shei

[mahoneypat]

Not totally sure on what you are looking for but you can make a table visual with your Store column and this measure.  It will show 1 if the B value for that store is blank (and 2 if not).

B Blank = IF(ISBLANK(CALCULATE(MIN(Table[Estimated Cost]), Table[Phases] = "B")), 1, 2)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[AllisonKennedy]

Not sure what the problem is? What formula do you want to use for Measure =1 and measure=2?

What configuration do you want to have in the final table display?

A simple IF or SWITCH should do what you want, unless you have a complex table configuration, then you may need to start looking at ALL or other similar options.

[shei7141]

Thank mate

I am having trouble with referencing table's columns in a measure. In excel, I would have said,

if(and(Phase = "B",Estimate Value >0), 1, 2)

It is a simple tab and simple if and condition but I am struggling to define it as a measure.

Ta

Shei

[AllisonKennedy · Accepted solution]

Yes, a MEASURE does not have the row context of the table. You can try two options:

Option A:
Use SUMX(Table, IF( insert your formula here)

Option B:
Use the SELECTEDVALUE function around any column you want to reference. This will only return a value if there is 1 value for the selected column, so requires a specific table configuration.

I prefer Option A in this case.