Need Help with DAXquery using nested IF statement/Switch

Hello,

i’m looking for the correct dax to create nested if statement/switch to use in my scenario.
there is a case statement using SQl query but i would like to come up with DAX rather than SQl query.
here is the case statement:
when status = ‘in_transit’ and
batch_name is null and
(x.shipper_batch_name is null and x.tkt_shipper_batch_name is null)
then
‘Good - no ticket and no carry over’
when status = ‘in_transit’ and
batch_name is not null and
abs(NVL(sched_delivered_vol,observed_injected_vol) - a.INTRANSIT_VOLUME - tk.net_vol) <= 500 and --need scheduled vol
(x.shipper_batch_name is null and x.tkt_shipper_batch_name is null)
then
‘Good2 - Ticket found and vol matches’
when status = ‘in_transit’ and
(x.shipper_batch_name is not null or x.tkt_shipper_batch_name is not null)
then
‘Bad1 - found carry over’
when status = ‘in_transit’ and
batch_name is not null and
abs(NVL(sched_delivered_vol,observed_injected_vol) - a.INTRANSIT_VOLUME - tk.net_vol) > 500 --need scheduled vol
then
‘Bad2 - volumes not matching’
when status = ‘Deliveried Batch’ and
batch_name is not null and
abs(nvl(Tk.net_VOL,0) - nvl(a.DELIVERED_VOL,0)) <= 500
then
‘Good3 - Ticket found and vol matches’

there are other status with similar conditions adding up to this query , have just copied only for two but in total we have 3 status condition when status=‘in_transit’ ,status=‘Deliveried Batch’ and status = Blank.

image

is it good to show in sql query or by using measure or calculated column? if it is by measure /calculated column, can you help me with the dax.

Thanks,
Priya

is it good to show in sql query or by using measure or calculated column?

Your question isn’t really clear to me.

But if you need to apply this logic to each row of a certain table and add the result to a table, you can achieve that through Power Query using M (on the modeling side) OR using DAX (on the report side).

Training resources for the requested DAX functions below, perhaps they can help you on your way.

So if my assumption is correct the result of the IF/SWITCH statement will be a “label” for each record.
If this logic never changes then I would suggest handling it in Power Query.
Although you can achieve exactly the same result with DAX. If you choose the latter how you use this “label” will determine if you need a measure or calculated column. Say you need to slice by that “label” in your report a calculated column would be best, since there are only a few defined outcomes this should have a limited impact on the size of your data model.
In all other cases a measure is prefered. The DAX needed for a measure or calculated column will differ slightly.

For better assitance please be more explicit in describing your needs and provide a sample PBIX.

Hi Melissa,

Thank you for the feedback. let me be more precise on my post

i actually want some help with the DAX so i can slice by the Label. and the logic never changes in future so i need to use same logic that has been used in my Query.
Logic:
case
when status = ‘in_transit’ and
batch_name is null and
(x.carry_shipper_batch_name is null and x.tkt_shipper_batch_name is null)
then
‘Good - no ticket and no carry over’
when status = ‘in_transit’ and
batch_name is not null and
abs(NVL(sched_delivered_vol,observed_injected_vol) - a.INTRANSIT_VOLUME - tk.net_vol) <= 500 and --need scheduled vol
(x.carry_shipper_batch_name is null and x.tkt_shipper_batch_name is null)
then
‘Good2 - Ticket found and vol matches’
when status = ‘in_transit’ and
(x.carry_shipper_batch_name is not null or x.tkt_shipper_batch_name is not null)
then
‘Bad1 - found carry over’
when status = ‘in_transit’ and
batch_name is not null and
abs(NVL(sched_delivered_vol,observed_injected_vol) - a.INTRANSIT_VOLUME - tk.net_vol) > 500 --need scheduled vol
then
‘Bad2 - volumes not matching’
when status = ‘Deliveried Batch’ and
batch_name is not null and
abs(nvl(Tk.net_VOL,0) - nvl(a.DELIVERED_VOL,0)) <= 500
then
‘Good3 - Ticket found and vol matches’
when status = ‘Deliveried Batch’ and
batch_name is null and
abs(delivered_vol - carry_over_vol) <= 500
then
‘Good4 - no ticket carry over vol match -is this valid’
when status = ‘Deliveried Batch’ and
batch_name is null and
abs(delivered_vol - Nvl(carry_over_vol,0)) > 500
then
‘Bad 4 no ticket carry over vol does not match -is this valid’
when status = ‘Deliveried Batch’ and
batch_name is not null and a.shipper_batch_name is not null and
abs(nvl(Tk.net_VOL,0) - nvl(a.DELIVERED_VOL,0)) > 500
then
‘Bad5 - Ticket found and vol does not match’
when tk.batch_name is not null and a.shipper_batch_name is null
then
‘Bad6 - Ticket found and no shipper batch’
else
NULL
end DL_INVESTIGATE,
case
when status = ‘Deliveried Batch’ and
batch_name is null and
abs(delivered_vol - Nvl(carry_over_vol,0)) > 500
then
abs(delivered_vol - Nvl(carry_over_vol,0))
when status = ‘Deliveried Batch’ and
batch_name is not null and a.shipper_batch_name is not null and
abs(nvl(Tk.net_VOL,0) - nvl(a.DELIVERED_VOL,0)) > 500
then
abs(nvl(Tk.net_VOL,0) - nvl(a.DELIVERED_VOL,0))
else
0
case
when status = ‘in_transit’ and
batch_name is null and
(x.carry_shipper_batch_name is null and x.tkt_shipper_batch_name is null)
then
‘Good - no ticket and no carry over’
when status = ‘in_transit’ and
batch_name is not null and
abs(NVL(sched_delivered_vol,observed_injected_vol) - a.INTRANSIT_VOLUME - tk.net_vol) <= 500 and --need scheduled vol
(x.carry_shipper_batch_name is null and x.tkt_shipper_batch_name is null)
then
‘Good2 - Ticket found and vol matches’
when status = ‘in_transit’ and
(x.carry_shipper_batch_name is not null or x.tkt_shipper_batch_name is not null)
then
‘Bad1 - found carry over’
when status = ‘in_transit’ and
batch_name is not null and
abs(NVL(sched_delivered_vol,observed_injected_vol) - a.INTRANSIT_VOLUME - tk.net_vol) > 500 --need scheduled vol
then
‘Bad2 - volumes not matching’
when status = ‘Deliveried Batch’ and
batch_name is not null and
abs(nvl(Tk.net_VOL,0) - nvl(a.DELIVERED_VOL,0)) <= 500
then
‘Good3 - Ticket found and vol matches’
when status = ‘Deliveried Batch’ and
batch_name is null and
abs(delivered_vol - carry_over_vol) <= 500
then
‘Good4 - no ticket carry over vol match -is this valid’
when status = ‘Deliveried Batch’ and
batch_name is null and
abs(delivered_vol - Nvl(carry_over_vol,0)) > 500
then
‘Bad 4 no ticket carry over vol does not match -is this valid’
when status = ‘Deliveried Batch’ and
batch_name is not null and a.shipper_batch_name is not null and
abs(nvl(Tk.net_VOL,0) - nvl(a.DELIVERED_VOL,0)) > 500
then
‘Bad5 - Ticket found and vol does not match’
when tk.batch_name is not null and a.shipper_batch_name is null
then
‘Bad6 - Ticket found and no shipper batch’
else
NULL
end DL_INVESTIGATE,
case
when status = ‘Deliveried Batch’ and
batch_name is null and
abs(delivered_vol - Nvl(carry_over_vol,0)) > 500
then
abs(delivered_vol - Nvl(carry_over_vol,0))
when status = ‘Deliveried Batch’ and
batch_name is not null and a.shipper_batch_name is not null and
abs(nvl(Tk.net_VOL,0) - nvl(a.DELIVERED_VOL,0)) > 500
then
abs(nvl(Tk.net_VOL,0) - nvl(a.DELIVERED_VOL,0))
else
0

-Thanks

@Priya

I’m not familiar with SQL so I had to Google NVL() looks like it’s used to replace a NULL value.
Seems your column headers don’t match with the SQL statement above… but I figure you can easily fix any reference mistakes I might have made (in the variables).
I also found that records could both match the criteria set for Bad1 & Bad2 but SWITCH will always return the first evaluation resulting in a TRUE so you might want to take another look at that.

Note. I prefer using variables but that is not required. The double && stand for AND, the double || stands for OR. Here’s the DAX for the calculated column:

myLabel = 
VAR LineStatus = tdata[STATUS]
VAR BatchName = tdata[BATCH_NAME]
VAR ShpBatchName = tdata[SHIPPER_BATCH_NAME]
VAR TkShpBatchName = tdata[TKT_SHIPPER_BATCH_NAME]
VAR SchedDelVol = tdata[SCHED_DELIVERED_VOL]
VAR ObsInjVol = tdata[OBSERVED_INJECTED_VOL]
VAR IntraVol = tdata[INTRANSIT_VOLUME]
VAR NetVol = tdata[NET_VOL]
VAR DelVol = tdata[DELIVERED_VOL]
RETURN

SWITCH( TRUE(),
   LineStatus = "in_transit", 
   SWITCH( TRUE(),
        ISBLANK(BatchName) && ISBLANK(ShpBatchName) && ISBLANK(TkShpBatchName) , "Good - no ticket and no carry over",
        NOT(ISBLANK(BatchName)) && ABS( IF(ISBLANK(SchedDelVol), ObsInjVol, SchedDelVol) - (IntraVol - NetVol)) <=500 , "Good2 - Ticket found and vol matches",
        NOT(ISBLANK(ShpBatchName)) || NOT(ISBLANK(TkShpBatchName)) , "Bad1 - found carry over" ,
        NOT(ISBLANK(BatchName)) && ABS( IF(ISBLANK(SchedDelVol), ObsInjVol, SchedDelVol) - (IntraVol - NetVol))  >500, "Bad2 - volumes not matching" 
    ),
   LineStatus = "Deliveried Batch", 
   SWITCH( TRUE(),
        NOT(ISBLANK( BatchName)) && ABS( IF( ISBLANK(NetVol),  0, NetVol) - IF( ISBLANK(DelVol), 0, DelVol)) <=500 , "Good3 - Ticket found and vol matches"
   )
)

Be sure to check out the previously linked resources.
in_trans Test.xlsx (344.9 KB) in_trans Test.pbix (280.3 KB)

Thank you much Melissa!!:slight_smile:

Hello Melissa,

there was another request was suggested for the above Dax statement. if you see in the dax for
LineStatus = “in_transit” /“Deliveried Batch” we are setting up a range of <=500 and > 500 in required case statements. so the question asked here is if we have range slicer from 0-1000 so user can have
the feasibility to select the range as per his need. the range that am using now is not standard so they want to come up with the slicer.

in order to do that i have created a table with manually entered range and named as “discrepancy range” also created measure using selected value
selected range = SELECTEDVALUE(‘Discrepancy Range’[Discrepancy Range],0-100)

but am not sure how to use or fit that logic in the dax that you have created.

am attaching my pbix file which i try to fit the logic but am missing something please help me.

Thanks,Priya
In_trans_test.pbix (406.1 KB)

Hi Priya,

Sam has showcased this technique in the Power BI & DAX Master Class: Advanced Analytics w/Power BI just last week (sorry you’ve missed it) and in other rescources also, here’s one:

See if this helps you on your way.
Melissa

Hi Melissa,

Thank you for the reply… those showcase techniques are quite similar to setting up the range in my case…but i try to use that in my measure “selected range” and want to use that in the same existing dax in “myLabel” which you have created for the first scenario. i try to use selected range measure by replacing<= 500 &> 500 with “selected range” measure but it is not working…result are not showing correct values, thought it was a simple fix but i guess am missing out with functions.

image

Hi Priya,

Please share the latest version of you PBIX file and I’ll take a look.

Hi,
Here is the file.

In_trans_test.pbix (385.0 KB)

i try to change range from text to whole number but still the result doesn’t match.

if you do the math on bold characters the results are not showing as per the logic:
ABS(IF(ISBLANK(SchedDelVol),ObsInjVol,SchedDelVol) - IntraVol - NetVol) <=[selected range], “Good2 - Ticket found and vol matches” ,
ABS(IF(ISBLANK(SchedDelVol),ObsInjVol,SchedDelVol) - IntraVol - NetVol) > [selected range], “Bad2 - volumes not matching” ,

for example: for range 100 , Batch name CHV-252-0 , SchedDelVol = 9,326.00, IntraVol = 4,247.00, NetVol = 5,097.16

if you do math as per our logic all bad 2 results are not bad 2 they are good 2.
Investigate should match with DL_investigate you see that in detailed in_trans volume tab.

Thanks,
Priya

I think there’s an easy fix because your range isn’t type number but text thats way you cannot run any calculations over it…

fix

i try to change range from text to whole number but still the result don’t match.

if you do the math on bold characters the results are not showing as per the logic:
ABS(IF(ISBLANK( SchedDelVol),ObsInjVol,SchedDelVol) - IntraVol - NetVol ) <=[selected range], “Good2 - Ticket found and vol matches” ,
ABS(IF(ISBLANK( SchedDelVol),ObsInjVol,SchedDelVol) - IntraVol - NetVol ) > [selected range], “Bad2 - volumes not matching” ,

for example: for range 100 , Batch name CHV-252-0 , SchedDelVol = 9,326.00, IntraVol = 4,247.00, NetVol = 5,097.16

if you do math as per our logic all bad 2 results are not bad 2 they are good 2.
Investigate should match with DL_investigate you see that in detailed in_trans volume tab.

can you supply the sample data as well?
Because when I filter on CHV-252-0 in your tabel, the table is empty…

don’t filter directly on the detailed In_trans_volume page. if you do from “In_Trans” page there is table if you look at the status column for In_transit and under investigate Bad2 when you right click on bad2 you can drill through to Detailed In_trans volume page you can see all bad2 filtered on that canavas. the very first one has the batch name CHV-252-0.

sample data for in_transit.csv (1.7 KB)

I’ve added an EVAL page where I broke the formula down for you. If you expect a different result then please check your logic… because the [Test value] > [selected range] which returns Bad2
don’t forget that the ABS function returns a number without the sign, see for documentation:
https://docs.microsoft.com/en-us/dax/abs-function-dax

And another important note: calculated columns are computed during the database processing and then stored in the model. To show what that means I’ve added the following calculated column to your table data[TEST value] = [selected range] please see what happens when you change [selected range]…

So I’ve added an [Investigate measure] see result below when I changed the [selected range] to 20

here’s your file:
In_trans_test (1).pbix (402.0 KB)

Hi Melissa,

again i really appreciate you taking time to fix this.
the measure looks good when i add all the columns to it. but i need to show in first page i just need to show only a table with limited columns as you can see in my In_trans page. when i try to bring
investigate measure to In_trans page i get different view.

for in_transit it is showing just "good-no ticket and no carry over " which is not correct.
i need to showcase all the Good and Bads for each status.

Thanks,PriyaIn_trans_test (1).pbix (409.1 KB)

The reason why you get a different result for the [Investigate measure] on the second page is the filter context that table provides with the additional columns. See if you can fix that, just have a go…

By the way did you check your switch logic and is that resolved now?

Post any progress you make, if still needed I’ll have a look this weekend.

@Priya

For more advanced logic it’s realy important to set up your model in a certain way. Please go through:

And in this module the section on “Designing advanced data models”

Introduce lookup tables like a Date table and other relevant Dimension tables you need.
This will make DAX required to solve business problems much easier.

Hi Melissa,

the logic for Good and bad conditions are correct, just there will be a change in logic with the carry over volume for delivered batch status.

i try to change filter context by adding calculate to the measure but still not working.

thanks for the post will take a look of data modeling that needs to improve at my end.

Thanks,
Priya

Hi Melissa/Sam,

I tried all possibilities by changing filter context also try to change to data model for Range still not getting correct result.i want to know is it possible to fix this or is it something i need to discuss to change the requirement?.
i want to show my report something like this:

which is not showing by using that measure investigate 2. i try to apply filters on each switch function but no result. please help me in fixing this.

Thanks,
PriyaIn_trans_test.pbix (388.6 KB)