Forum Discussion
Using switch in M
Hi all,
I have an easy problem, but as I am a beginner i M I have a bit proglem with this. I have the following calculation in dax swithing between several dates. However, I need to make the same calculation in power query and what i have been trying is not working does not pull the dates correctly:
I have following:
Date = SWITCH(
TRUE();
'table1'[Real Finish]<> BLANK(); 'table1'[Real Finish];
'table1'[Remaining Finish] <> BLANK(); 'table1'[Remaining Finish];
'table1'[Actual Finish]<> BLANK(); 'table1'[Actual Finish];
'table1'[Real Start]<> BLANK(); 'table1'[Real Start];
'table1'[Remaining Start] <> BLANK(); 'table1'[Remaining Start];
'table1'[Actual Start])
Can some of you help me with this?
Thank you for your time,
Anastasia
Hi AU555,
you can try something like this, try adding a custom column with the following code
if [C1] = null then [C1_1]
else
if [C2] = null then [C2_1]
else
if [C3] = null then [C3_1]
else [C4]
This would be equivalent to the following. IUse this pattern to build exactly what you need
ColumnName = SWITCH(
TRUE();
'table1'[C1]<> BLANK(); 'table1'[C1_1];
'table1'[C2] <> BLANK(); 'table1'[C2_1];
'table1'[C3]<> BLANK(); 'table1'[C3_1];
'table1'[C4])
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
3 Replies
Hi AU555,
you can try something like this, try adding a custom column with the following code
if [C1] = null then [C1_1]
else
if [C2] = null then [C2_1]
else
if [C3] = null then [C3_1]
else [C4]
This would be equivalent to the following. IUse this pattern to build exactly what you need
ColumnName = SWITCH(
TRUE();
'table1'[C1]<> BLANK(); 'table1'[C1_1];
'table1'[C2] <> BLANK(); 'table1'[C2_1];
'table1'[C3]<> BLANK(); 'table1'[C3_1];
'table1'[C4])
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
I am a bit confused: this calculation in DAX says: if C1 is not blank, use C1_1, if C2 is not blank, use C2_1 (<> is no eqal to)
ColumnName = SWITCH(
TRUE();
'table1'[C1]<> BLANK(); 'table1'[C1_1];
'table1'[C2] <> BLANK(); 'table1'[C2_1];
'table1'[C3]<> BLANK(); 'table1'[C3_1];
'table1'[C4])
and the calculation in M:
if [C1] = null then [C1_1]
else
if [C2] = null then [C2_1]
else
if [C3] = null then [C3_1]
else [C4]
says, if C1 is null then use C1_1, is C2 is null then use C2
But I need calculation saying, If C1 is not blank use C1, if C1 is blank use C2, if C2 is not blank use C2, if C2 is blank use C3 etc....
