Measure, nested-IF, text

[Anonymous]

Hello to all DAX gurus,

source_table:

Location (text)	Phase (text)	Id (number)	Value (number)
A	P1	1	10
B	P1	2	10
B	P2	3	10
C	P2	4	10
C	P3	5	10

Logic:

For each location:

if Phase- P1, calculated (Value; FILTER (Source_table; Phase - P1));

if Phase- P2, is calculated (Value; FILTER (Source_table; Phase - P2));

if Phase- P3, calculated (Value; FILTER (Source_table; Phase - P3));

Explanation:

For each location, if there is a P1 value, return the value P1.

If there is no P1 value, use the value P2.

If there is no P2 value, use the P3 value.

How can I achieve a result table like the following, with the above logic, using only the measure? I use the SAAS model, so I can only create a new measure (no calculated columns).

Result_table:

Location	Phase	Id	new_measure
A	P1	1	10
B	P1	2	10
C	P2	4	10

I tried to use RANKX, KEEPFILTER, ISFILTERED, but so har has not succeeded.

Thank you in advance 🙂

[v-eachen-msft]

Hi @Anonymous ,

If your fact table have same order as sample, you could try the following DAX:

Measure  =
IF (
    SELECTEDVALUE ( 'Table'[Id] )
        = CALCULATE ( MIN ( 'Table'[Id] ), ALLEXCEPT ( 'Table', 'Table'[Location] ) ),
    [Your value]
)

Here is the result.

[Anonymous]

@v-eachen-msft  thanks for the help, but the ID is random unfortunately, so I can't use MIN. I'm still trying to understand why I get all the Phase on the result table.

[parry2k]

@Anonymous really not sure what does this means:

For each Location:

if Phase= P1, get Value;

else if Phase= P2, get Value;

else if Phase= P3, get Value

all are getting value?

[Anonymous]

@parry2k I added explanation to the problem above. Sorry if it wasn't clear.

[parry2k · Accepted solution]

@Anonymous try this

Measure = 
VAR __p1 = CALCULATE ( [Value Measure], Table[Phase] = "P1" )
VAR __p2 = CALCULATE ( [Value Measure], Table[Phase] = "P3" )
VAR __p3 = CALCULATE ( [Value Measure], Table[Phase] = "P3" )
RETURN
SWITCH ( TRUE(),
 __p1 <> BLANK(), __p1,
 __p2 <> BLANK(), __p2,
 __p3
)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[Anonymous]

@parry2k the result_table is correct if it only has Location, ID, and new_measure columns, but when I added the Phase column, it got messed up and showed duplicated Location, new_measure value for all the Phase P1-P3. How can I avoid this? Thanks!