Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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:
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
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 ()
)
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:
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
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 ()
)
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
Variables are brand new so I'm going to give it a crack 🙂
Thank you
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |