Using switch in M

[AU555]

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

[AlB · Accepted solution]

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  

[AlB · Accepted solution]

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  

[AU555]

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....

[AlB · Accepted solution]

@AU555 

You're right. Make it <> null in all cases instead of    = null

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers