Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

How to code this in DAX

Hi all,    I am trying to code below in DAX, can someone please help !   CASE WHEN MMIT_MEDICAL.is ='1' AND MMIT_MEDICAL.pa = '0' AND MMIT_MEDICAL.st = '0' THEN 'Covered'    WHEN MMIT_MEDICAL.i...
Need Help
Tips and Tricks
  • dm-p's avatar
    dm-p
    7 years ago

    Something like the following should work (assuming columns in your data model are the same as SQL):

     

    SWITCH (
    TRUE (),
    'MMIT_MEDICAL'[is] = "1"
        && 'MMIT_MEDICAL'[pa] = "0"
        && 'MMIT_MEDICAL'[st] = "0", "Covered",
    'MMIT_MEDICAL'[is] = "1"
        && ( 
                    'MMIT_MEDICAL'[pa] <> "0"
             || 'MMIT_MEDICAL'[st] <> "0" 
            ), "Covere(PA/ST)",
    "Unknown"
)
  • dm-p's avatar
    dm-p
    7 years ago

    Hi Anonymous - as you were using single quotes around your values in your SQL, I assumed you had the same requirement in Power BI, i.e. your fields were text-based. Having seen your error, I suspect that they are being treated as numbers in your data model.

    I have amended the formula to treat as numbers (removing the quotation marks around the numbers when comparing). Can you please try the amended one below and see if that works?

    SWITCH (
    TRUE (),
    'MMIT_MEDICAL'[is] = 1
        && 'MMIT_MEDICAL'[pa] = 0
        && 'MMIT_MEDICAL'[st] = 0, "Covered",
    'MMIT_MEDICAL'[is] = 1
        && ( 
                'MMIT_MEDICAL'[pa] <> 0
             || 'MMIT_MEDICAL'[st] <> 0 
        ), "Covere(PA/ST)",
    "Unknown"
)

    If this doesn't work, then please provide some more information about the fields you're using in this calculation, e.g. data types and we'll see where we can go from there.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

hi dm-p , 

 

I am getting this error when i using the code - 

 

DAX comparison operations do not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

 

What do you suggest ?

dm-p's avatar
dm-p
Super User
7 years ago

Hi Anonymous - as you were using single quotes around your values in your SQL, I assumed you had the same requirement in Power BI, i.e. your fields were text-based. Having seen your error, I suspect that they are being treated as numbers in your data model.

I have amended the formula to treat as numbers (removing the quotation marks around the numbers when comparing). Can you please try the amended one below and see if that works?

SWITCH (
    TRUE (),
    'MMIT_MEDICAL'[is] = 1
        && 'MMIT_MEDICAL'[pa] = 0
        && 'MMIT_MEDICAL'[st] = 0, "Covered",
    'MMIT_MEDICAL'[is] = 1
        && ( 
                'MMIT_MEDICAL'[pa] <> 0
             || 'MMIT_MEDICAL'[st] <> 0 
        ), "Covere(PA/ST)",
    "Unknown"
)

If this doesn't work, then please provide some more information about the fields you're using in this calculation, e.g. data types and we'll see where we can go from there.