cancel
Showing results for
Did you mean:
Post Patron

## Replicate Excel Formula in Dax

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 !

3 ACCEPTED SOLUTIONS
Super User III
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?

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.

www.excelwithallison.com

Super User IV

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Super User III
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).

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.

www.excelwithallison.com

6 REPLIES 6
Super User III
I'm not sure I understand your problem, but would adding an IF(ISBLANK(VAR CCI),

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.

www.excelwithallison.com

Post Patron

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:

Super User IV

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Super User III
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?

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.

www.excelwithallison.com

Post Patron

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:

Super User III
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).

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.

www.excelwithallison.com

Announcements