cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cristianml
Post Patron
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"))

 

risk.jpg

 

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.

error risk.jpg

 

Could you help me ?

 

Thanks !

3 ACCEPTED SOLUTIONS

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. 


If you found this post helpful, please give Kudos. ?


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


www.excelwithallison.com

View solution in original post

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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

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. 


If you found this post helpful, please give Kudos. ?


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


www.excelwithallison.com

View solution in original post

6 REPLIES 6
AllisonKennedy
Super User III
Super User III

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

before your current DAX help?


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. 


If you found this post helpful, please give Kudos. ?


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


www.excelwithallison.com

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:

before.png

 

After:

after.png 

 

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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

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. 


If you found this post helpful, please give Kudos. ?


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


www.excelwithallison.com

View solution in original post

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:

 

relation.png

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. 


If you found this post helpful, please give Kudos. ?


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


www.excelwithallison.com

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors