Replicate Excel Formula in Dax

[cristianml]

Hi,

I need to replicate this formula from Excel to DAX as a Measure but not sure how:

=+IF(K6<-50000,"High Risk",IF(L6<-0.9%,"Medium Risk","No Risk"))

I tried this way but is showing wrong in the pivot table: When i drag the measure into the pivot table it shows like a breakdown but I don't want that. I need to evaluate same way like in Excel.

Could you help me ?

Thanks !

[AllisonKennedy · Accepted solution]

You need to swap the order of your measure or use

IF(NOT(ISBLANK(Var CCI))), ...
then the rest of your measure.

But the Measure isn't the problem here, the problem is that you don't have any relationship between Contract Name and Contract Number tables, so you need to fix your data model relationships and get some best practice in there. Why are they in separate tables?

[mahoneypat · Accepted solution]

Your logic always returns at least "No Risk", so it is added rows that would normally be blank (and be auto filtered from the visual).  Please try this expression instead (correct the logic if incorrect, but it will return the result for the first one that is true).

New Measure =
SWITCH (
    TRUE (),
    [Var CCI] <= 50000, "High Risk",
    [Var CCI%] < 0.9, "Medium Risk",
    NOT ( ISBLANK ( [Var CCI] ) ), "No Risk",
    BLANK ()
)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[AllisonKennedy]

I'm not sure I understand your problem, but would adding an IF(ISBLANK(VAR CCI),

before your current DAX help?

[cristianml]

Hi @AllisonKennedy ,

I tried that using "blank" but is not working. I think that is not the solution. I only have 35 lines and the problem is that when I drag the measure into the pivot table it shows 1260 lines and that is not what I spect. I think the issue is how I need to evaluate the information in the model. 

See the difference: 

Before use the measure:

After:

 

[mahoneypat · Accepted solution]

Your logic always returns at least "No Risk", so it is added rows that would normally be blank (and be auto filtered from the visual).  Please try this expression instead (correct the logic if incorrect, but it will return the result for the first one that is true).

New Measure =
SWITCH (
    TRUE (),
    [Var CCI] <= 50000, "High Risk",
    [Var CCI%] < 0.9, "Medium Risk",
    NOT ( ISBLANK ( [Var CCI] ) ), "No Risk",
    BLANK ()
)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[AllisonKennedy · Accepted solution]

You need to swap the order of your measure or use

IF(NOT(ISBLANK(Var CCI))), ...
then the rest of your measure.

But the Measure isn't the problem here, the problem is that you don't have any relationship between Contract Name and Contract Number tables, so you need to fix your data model relationships and get some best practice in there. Why are they in separate tables?

[cristianml]

Hi @AllisonKennedy ,

Actually there is a relationship for Contract Name and Contract Number. I created Lists from an append table to do relationships for each category of the tables:

[AllisonKennedy · Accepted solution]

Glad you have got this working as you want, but just a small bonus lesson on the relationships - since you have Contract Name and Contract Number in two different tables, they actually have no relationship to each other, which is why you get every contract name listed for each contract number in your screenshot with the problem above. This is because they are both linked to the FT_Actual table, but only in the direction the arrow points. So from Contract Number we can get to FT_Actual, but not back to Contract Name. Your measures can do the filtering for you, as you have seen, but if there is only 1 Contract name for each Contract Number you might consider keeping them in the same table at some stage (maybe not now since it's working, but if you start to have another problem, consider reevaluating your data model setup).