DAX - Max or Median depending on criteria

[Anonymous]

Hi all,

I'm having a problem with a table visual summarising a particular number of transactions in this format:

STOCK #    DESCRIPTION   #CRITICALITY CODE   #ORDERS  #TIMES LATE   LEAD TIME   RECOMMENDED LEAD TIME

I would like my recommended lead time to be based on two conditions, which obviously the visual doesn't allow me to do so I was hoping DAX could help: if the item has a criticality A or B, then the LEAD TIME should be the MAX (or ideally 90% decile of the population) of all transactions. If the criticality is anything else, then it should be the Median of all transactions.

I have been trying for the whole day with various measures and columns, do you guys have any idea?

Thanks,

OF

[v-yuta-msft · Accepted solution]

Hi OzzieFrog,

Based on your description and my understanding, you need to create a calculate column or measure [RECOMMENDED LEAD TIME] which is based on conditions like below:

1. If column [CRITICALITY CODE] = “A”, then [RECOMMENDED LEAD TIME]  = MAX([LEAD TIME])
2. If column [CRITICALITY CODE] = “B”, then [RECOMMENDED LEAD TIME]  = value in 90% decile point of column [LEAD TIME]
3. If column [CRITICALITY CODE] <> “A” and [CRITICALITY CODE] <> “B”, then [RECOMMENDED LEAD TIME] = Median value of column [LEAD TIME]

I have created a sample data like below:

 

If my understanding is right, please refer to steps:

1.Click Query Editor -> Open Advanced Editor -> Sort rows by column [LEAD TIME] and Add index column [Rank] by M code Below:

#"Sorted Rows" = Table.Buffer(Table.Sort(#"Changed Type",{{"LEAD TIME", Order.Ascending}}))

#"MyRank" = Table.AddIndexColumn(#"Sorted Rows", "RANK",1,1)

Table1 after the two steps above is like below:

2. Now we can use column [RANK] to achieve value in 90% decile point of column [LEAD TIME] and Median value of column [LEAD TIME], create a measure and use DAX formula below:

RECOMMENDED LEAD TIME =

VAR Point1 = ROUNDUP(MAXX(ALL(Table1), [RANK]) * 0.9, 0)

VAR Point2 = ROUNDUP(MAXX(ALL(Table1), [RANK]) * 0.5, 0)

RETURN

SWITCH(MAX([CRITICALITY]), "A", MAXX(ALL(Table1), [LEAD TIME]), "B", CALCULATE(MAX([LEAD TIME]), ALL(Table1), Table1[RANK] = Point1), CALCULATE(MAX([LEAD TIME]), ALL(Table1), Table1[RANK] = Point2))

You can refer to PBIX here:

https://www.dropbox.com/s/z4jg8e4a8yctb9k/For%20OzzieFrog.pbix?dl=0

Best Regards,

Jimmy Tao

[v-yuta-msft · Accepted solution]

Hi OzzieFrog,

Based on your description and my understanding, you need to create a calculate column or measure [RECOMMENDED LEAD TIME] which is based on conditions like below:

1. If column [CRITICALITY CODE] = “A”, then [RECOMMENDED LEAD TIME]  = MAX([LEAD TIME])
2. If column [CRITICALITY CODE] = “B”, then [RECOMMENDED LEAD TIME]  = value in 90% decile point of column [LEAD TIME]
3. If column [CRITICALITY CODE] <> “A” and [CRITICALITY CODE] <> “B”, then [RECOMMENDED LEAD TIME] = Median value of column [LEAD TIME]

I have created a sample data like below:

 

If my understanding is right, please refer to steps:

1.Click Query Editor -> Open Advanced Editor -> Sort rows by column [LEAD TIME] and Add index column [Rank] by M code Below:

#"Sorted Rows" = Table.Buffer(Table.Sort(#"Changed Type",{{"LEAD TIME", Order.Ascending}}))

#"MyRank" = Table.AddIndexColumn(#"Sorted Rows", "RANK",1,1)

Table1 after the two steps above is like below:

2. Now we can use column [RANK] to achieve value in 90% decile point of column [LEAD TIME] and Median value of column [LEAD TIME], create a measure and use DAX formula below:

RECOMMENDED LEAD TIME =

VAR Point1 = ROUNDUP(MAXX(ALL(Table1), [RANK]) * 0.9, 0)

VAR Point2 = ROUNDUP(MAXX(ALL(Table1), [RANK]) * 0.5, 0)

RETURN

SWITCH(MAX([CRITICALITY]), "A", MAXX(ALL(Table1), [LEAD TIME]), "B", CALCULATE(MAX([LEAD TIME]), ALL(Table1), Table1[RANK] = Point1), CALCULATE(MAX([LEAD TIME]), ALL(Table1), Table1[RANK] = Point2))

You can refer to PBIX here:

https://www.dropbox.com/s/z4jg8e4a8yctb9k/For%20OzzieFrog.pbix?dl=0

Best Regards,

Jimmy Tao

[Anonymous · Accepted solution]

Thank you all for your suggestions.

After a lot of trial an error, I ended-up going with the following variable measure:

LT (BIC ADJST) =
VAR _median = MEDIAN ( 'Receipts & Overdues'[LT RECOMMENDED] )
VAR _90_perc = PERCENTILE.INC ( 'Receipts & Overdues'[LT RECOMMENDED], 0.90 )
RETURN
    IF (
        HASONEVALUE ( 'Receipts & Overdues'[Business Impact Code] ),
        IF (
            VALUES ( 'Receipts & Overdues'[Business Impact Code] ) = "A"
                || VALUES ( 'Receipts & Overdues'[Business Impact Code] ) = "B",
            _90_perc,
            _median
        ),
        BLANK ()
    )

[ricardocamargos]

Hi @Anonymous,

You can use variables and if function to handle this situation.

Check it out:

https://www.sqlbi.com/articles/variables-in-dax/

Ricardo

[Anonymous]

Variables are brand new so I'm going to give it a crack 🙂

Thank you