Switch IF and AND Expression Failing.-

[manuelfigueira]

Hi Guys,

I'm trying to work out why the DAX below is not working as I tried to say if the column [Purch.Doc] is blank or empty and the Profit Centre is F1001 return FOO and so on for each line.

So far like that only Return the Seg FOO for all of them.

Many thanks!!

SEG = SWITCH(TRUE(),
[Purch.Doc] = BLANK(), FORMAT([Profit Ctr] = "F1001", "FOO"),
[Purch.Doc] = BLANK(), FORMAT([Profit Ctr] = "G0003", "FOO"),
[Purch.Doc] = BLANK(), FORMAT([Profit Ctr] = "G0007", "C&H"),
[Purch.Doc] = BLANK(), FORMAT([Profit Ctr] = "G0009", "C&H"),
[Purch.Doc] = BLANK(), FORMAT([Profit Ctr] = "G1001", "C&H"),
[Purch.Doc] = BLANK(), FORMAT([Profit Ctr] = "L0002", "C&H"),
[Purch.Doc] = BLANK(), FORMAT([Profit Ctr] = "L0003", "FOO"),
BLANK())

[MartynRamsden · Accepted solution]

Hi @manuelfigueira 

Try this:

SEG =
SWITCH (
    TRUE (),
    [Purch.Doc] = BLANK() && [Profit Ctr] = "F1001", "FOO",
    [Purch.Doc] = BLANK() && [Profit Ctr] = "G0003", "FOO",
    [Purch.Doc] = BLANK() && [Profit Ctr] = "G0007", "C&H",
    [Purch.Doc] = BLANK() && [Profit Ctr] = "G0009", "C&H",
    [Purch.Doc] = BLANK() && [Profit Ctr] = "G1001", "C&H",
    [Purch.Doc] = BLANK() && [Profit Ctr] = "L0002", "C&H",
    [Purch.Doc] = BLANK() && [Profit Ctr] = "L0003", "FOO",
    BLANK ()
)

Remember - it's considered best pratice to prefix any column name with the table name ( e.g. Table1[Purch.Doc] )

Best regards,

Martyn

If I answered your question, please help others by accepting it as a solution.

[amitchandak · Accepted solution]

I think it should be something like this

SEG = SWITCH(TRUE(),
And([Purch.Doc] = BLANK(), FORMAT[Profit Ctr] = "F1001"), "FOO"),
And([Purch.Doc] = BLANK(), FORMAT[Profit Ctr] = "G0003"), "FOO"),
And([Purch.Doc] = BLANK(), FORMAT[Profit Ctr] = "G0007"), "C&H"),
And([Purch.Doc] = BLANK(), FORMAT[Profit Ctr] = "G0009"), "C&H"),
And([Purch.Doc] = BLANK(), FORMAT[Profit Ctr] = "G1001"), "C&H"),
And([Purch.Doc] = BLANK(), FORMAT[Profit Ctr] = "L0002"), "C&H"),
And([Purch.Doc] = BLANK(), FORMAT[Profit Ctr] = "L0003"), "FOO"),
BLANK())

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[MartynRamsden · Accepted solution]

Hi @manuelfigueira 

Try this:

SEG =
SWITCH (
    TRUE (),
    [Purch.Doc] = BLANK() && [Profit Ctr] = "F1001", "FOO",
    [Purch.Doc] = BLANK() && [Profit Ctr] = "G0003", "FOO",
    [Purch.Doc] = BLANK() && [Profit Ctr] = "G0007", "C&H",
    [Purch.Doc] = BLANK() && [Profit Ctr] = "G0009", "C&H",
    [Purch.Doc] = BLANK() && [Profit Ctr] = "G1001", "C&H",
    [Purch.Doc] = BLANK() && [Profit Ctr] = "L0002", "C&H",
    [Purch.Doc] = BLANK() && [Profit Ctr] = "L0003", "FOO",
    BLANK ()
)

Remember - it's considered best pratice to prefix any column name with the table name ( e.g. Table1[Purch.Doc] )

Best regards,

Martyn

If I answered your question, please help others by accepting it as a solution.