Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
NickzNickz_BI
Helper I
Helper I

Dax calculate based on multiple conditions of other columns

Hi.

 

I have created sample table as below. 

NickzNickz_BI_3-1668605516075.png

The status of the column will be displayed whether the KPI is Not Stated, Below Threshold, Threshold, Min or Meet Target based on the achievement of each KPI above.

 

Below is the formula I am currently using however I am unable to cater if the trend type is Descending. I just realized that I have such data and that is why I have added another column name Trend as my formula indicator. 

 

Formula

Achievement Status =

SWITCH(

TRUE(),

tbl_kpi_submission[achievement] => "0" && tbl_kpi_submission[achievement] < tbl_kpi_submission[Threshold],"Below Threshold",

tbl_kpi_submission[achievement] >= tbl_kpi_submission[Threshold] && tbl_kpi_submission[achievement] < tbl_kpi_submission[Min],"Threshold",

tbl_kpi_submission[achievement] >= tbl_kpi_submission[Min] && tbl_kpi_submission[achievement] < tbl_kpi_submission[Target],"Min",

tbl_kpi_submission[achievement] >= tbl_kpi_submission[Target],"Target","Not Started")

 

Appreciating someone can help me with this. I'm new and the above formula is basically what I got from youtube and manipulated into my situation.

 

Regards,

NickzNickz_BI

 

 

 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

@NickzNickz_BI Hi, Look at the attached file, corrected the steps according to the example you provided.



__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

View solution in original post

10 REPLIES 10
DimaMD
Solution Sage
Solution Sage

@NickzNickz_BI Hi, Provide a more detailed condition under which you want to get the result.
Under the condition that you wrote the measure, the measure will be as follows

 

 

 

Status_Colum = 
 SWITCH(
     TRUE(),
     AND([Archievement] >= 0 , [Archievement] < [Thereshold]) , "Below Threshold",
     AND([Archievement] >= [Thereshold], [Archievement] < [Min]), "Threshold",
     AND( [Archievement] >= [Min], [Archievement] < [Target]) , "Min",
     [Archievement] >= [Target], "Target" ,"Not Started"
 )
Status_Measure =
SWITCH (
    TRUE (),
    AND (
        SUM ( 'tbl_kpi_submission'[Archievement] ) >= 0,
        SUM ( 'tbl_kpi_submission'[Archievement] )
            < SUM ( 'tbl_kpi_submission'[Thereshold] )
    ), "Below Threshold",
    AND (
        SUM ( 'tbl_kpi_submission'[Archievement] )
            >= SUM ( 'tbl_kpi_submission'[Thereshold] ),
        SUM ( 'tbl_kpi_submission'[Archievement] ) < SUM ( 'tbl_kpi_submission'[Min] )
    ), "Threshold",
    AND (
        SUM ( 'tbl_kpi_submission'[Archievement] ) >= SUM ( 'tbl_kpi_submission'[Min] ),
        SUM ( 'tbl_kpi_submission'[Archievement] )
            < SUM ( 'tbl_kpi_submission'[Target] )
    ), "Min",
    SUM ( 'tbl_kpi_submission'[Archievement] )
        >= SUM ( 'tbl_kpi_submission'[Target] ), "Target",
    "Not Started"
)

 

 

 

Screenshot_10.jpg
Screenshot_10.jpg


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

Hi @DimaMD ,

 

This is my further explanation as requested.

 

Current result:

NickzNickz_BI_0-1668642942739.png

 

The result should be like this:

NickzNickz_BI_1-1668643130339.png

Based on the input in the Trend column, it will determine the formula that should be used to produce the correct result. If column Trend is Ascending then run the formula for Ascending or else run the formula for descending.

 

Regards,

NickzNickz

 

 

@NickzNickz_BI Hi, Look at the attached file, corrected the steps according to the example you provided.



__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

Dear @DimaMD ,

 

Hi ... I already tested both ... Thank you so much for the solution given. The only thing rite now is for me to understand on the coding side... 😁

 

Btw, instead of we create the trend column, can DAX read the trend of the value wether ascending or descending an immediately know how to calculate or respond to it.

 

Regards,

NickzNickz_BI

Hi @NickzNickz_BI  I don't quite understand what you mean
"Btw, instead of we create the trend column, can DAX read the trend of the value wether ascending or descending an immediately know how to calculate or respond to it."


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

 

What I mean is, if the trend of value for threshold , min and target is descending then the result should be like item no 5 as above or else ....

 

But is ok .... 

 

Btw below is my test result.

NickzNickz_BI_0-1668693484599.png

I just noticed that for Status_Column, some results are wrong compared to Status Measure... If my achievement is more that target, it show differently but it is ok since I still can use measure to populate the result... Thanks again. 👍

 

Regards.

NickzNickz_BI

 

 

 

@NickzNickz_BI Sorry I made a mistake,
Calculate colum :

Status_Colum = 
SWITCH (
    TRUE (),
    AND ( [Archievement] > 0, [Archievement] < [Thereshold] ), "Below Threshold",
    AND ( [Archievement] >= [Thereshold], [Archievement] < [Min] ), "Threshold",
    AND ( [Archievement] >= [Min], [Archievement] < [Target] ), "Min",
     [Archievement] >= [Target], "Target",
    "Not Started"
)

 


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

 

Hi @DimaMD ,

 

I run a few more testing with diferrent value for item #4 which is descending order.

Below is my test script output.

 

Test Script #1

Expected Output : Min

Test Script Output : Below Threshold

NickzNickz_BI_0-1668738652309.png

 

Test Script #2

Expected Output : Below Threshold

Test Script Output : Target

NickzNickz_BI_1-1668738737323.png

 

Test Script #3

Expected Output : Min

Test Script Output : Below Threshold

NickzNickz_BI_2-1668738815792.png

 

Test Script #4

Expected Output : Target

Test Script Output : Target

NickzNickz_BI_3-1668739262748.png

Based on above test script, still got problem to calculate the result for descending order...

 

 

 

Regards,

NickzNickz_BI

 

 

Hi @NickzNickz_BI  
Test Script #1 
Explain why it should be "Min" 


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

Dear @DimaMD ,

 

Hope this can explain why ... 

NickzNickz_BI_0-1669030324101.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors