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 Everybody,
I have a problem with my DAX to create a measure that gives me some information about the type of an order.
I tried already different versions (even calculated columns) but do not get the right values.
Hope someone can give me a hint.
My scenario:
I want to identify within all my orders: how many of these orders are 'sample orders', how many are 'normal/full orders' and how many are a combination of both (order does contain positions that would qualify as a sample, but also positions that qualify as full)
I have a fact table that contains all my order rows (order position of each order of each customer).
My order lines/positions have a 'quantity' field, that shows me how much of one article was ordered.
I define via a calculated column, if a row qualifies as 'sample position' (IsSample=IF(order_lines[quantity]<3, TRUE(), FALSE()))
To get 'Amount of Sample orders' I wanted to DISTINCTCOUNT the order-no of all rows where this is true
I used:
- but this is wrong as it would give me also the orders that qualify as a combination. (orders that have at least one position where the statement is true)
So I need to check for all order_lines if this statement is true.
(And then I would do the same other way araound: all orders where for all order_lines the statement is False, to get all normal/full orders. And then I would calculate the combination-orders (mixture of both) by Counting all orders and subtracting my sample-orders and my normal-orders. )
Then I tried this:
To find out how many orders only contain order-lines where the statement is true, I separated the order-no. in a new table and linked my new 'order_header' table to 'order_lines' via the order_no as a key. The relation is: one order_header can have several order_lines, but one order_line can only link to one order_header. The relation dows work fine (tested it via visuals)
Now I try to do it like I would do it with a SQL JOIN - assuming that the relation between those two tables are enough and would use this DAX:
this seems working better, as I get a result - but I get the same value for each customer, when I use it in a visual:
So I have the feeling I am close to the solution.
I thought the same value in each field is a problem that I would avoid with 'KEEPFILTERS', but seems not to be the problem here.
I already tried Versions of the above with 'FILTER' instead of 'KEEPFILTERS' and I also mixed in a RELATED() ... but nothing helped.
Am I totally wrong with my approach and the solution would be something completely different?
Should I go with a CROSSJOIN or HASVALUE function to solve such a problem?
Hope someone has an idea and could point me in a new direction.
Thanks in advance!
Solved! Go to Solution.
Hi @backflash
you start with creating a calculated column
Order Type =
VAR T1 =
CALCULATETABLE (
'order_lines',
ALLEXCEPT ( 'order_lines', 'order_lines'[order-no] )
)
VAR MaxQty =
MAXX ( 'T1', 'order_lines'[Quantity] )
VAR MinQty =
MINX ( 'T1', 'order_lines'[Quantity] )
VAR Result =
IF (
MaxQty < 3,
"Sample Order",
IF ( MinQty < 3, "Mixed Order", "Normal Order" )
)
RETURN
Result
you measure would be only
Number of Orders =
DISTINCTCOUNT ( 'order_lines'[order-no] )
Add the new column to the columns of the matix visual and the measure to the values.
Hi @backflash
per each order number, MAXX will return the maximum quantity and MINX will return the minumum quantity. If the maximum order quantity is less than three then all the order quantities are less than 3 therfore the first IF statment returns "Simple Order" otherise the nested IF statement checks if the minimum quantity is greater than 3 (and here is the error in the code) then it shall return " Normal Order" otherwise it returns "Mixed Order"
Order Type =
VAR T1 =
CALCULATETABLE (
'order_lines',
ALLEXCEPT ( 'order_lines', 'order_lines'[order-no] )
)
VAR MaxQty =
MAXX ( 'T1', 'order_lines'[Quantity] )
VAR MinQty =
MINX ( 'T1', 'order_lines'[Quantity] )
VAR Result =
IF (
MaxQty < 3,
"Sample Order",
IF ( MinQty >= 3, "Normal Order", "Mixed Order" )
)
RETURN
Result
Hi @backflash
you start with creating a calculated column
Order Type =
VAR T1 =
CALCULATETABLE (
'order_lines',
ALLEXCEPT ( 'order_lines', 'order_lines'[order-no] )
)
VAR MaxQty =
MAXX ( 'T1', 'order_lines'[Quantity] )
VAR MinQty =
MINX ( 'T1', 'order_lines'[Quantity] )
VAR Result =
IF (
MaxQty < 3,
"Sample Order",
IF ( MinQty < 3, "Mixed Order", "Normal Order" )
)
RETURN
Result
you measure would be only
Number of Orders =
DISTINCTCOUNT ( 'order_lines'[order-no] )
Add the new column to the columns of the matix visual and the measure to the values.
Hi @tamerj1 ,
many thanks for your suggestion.
Sadly I do only see 'Mixed order' as values in my newly created columns. There are no 'sample orders' or 'normal orders' with the code you provided.
I guess it is in the Result part, where something whould be different.
I do not understand fully what happens when you use 'MAXX' and 'MINX' for the order-lines-quantities. Don't you aggregate them that way?
Hi @backflash
per each order number, MAXX will return the maximum quantity and MINX will return the minumum quantity. If the maximum order quantity is less than three then all the order quantities are less than 3 therfore the first IF statment returns "Simple Order" otherise the nested IF statement checks if the minimum quantity is greater than 3 (and here is the error in the code) then it shall return " Normal Order" otherwise it returns "Mixed Order"
Order Type =
VAR T1 =
CALCULATETABLE (
'order_lines',
ALLEXCEPT ( 'order_lines', 'order_lines'[order-no] )
)
VAR MaxQty =
MAXX ( 'T1', 'order_lines'[Quantity] )
VAR MinQty =
MINX ( 'T1', 'order_lines'[Quantity] )
VAR Result =
IF (
MaxQty < 3,
"Sample Order",
IF ( MinQty >= 3, "Normal Order", "Mixed Order" )
)
RETURN
Result
Hi @tamerj1 ,
thanks for the explanation. Now I got the reason behind it. A nice way to do it!
But sadly it does not work, even when I correct the calculation ('>=' instead of '<' in the second IF clause).
Before correcting this error, it gave me 'Normal ordera' for all 35.000 orders - now with corrected option it gives me 'mixed order' for all the orders.
If it gives me 'Mixed order', that means that MaxQty is always smaller than 3 and MinQty is always bigger than 3 ... which is strange, because that's definitively not the case.
Could it be that by grouping all orderlines by order-no (which CALCULATETABLE does, right?) , he is summarizing all the order-line quantities and therefor does no longer see a Minquantity lower dann 3?
I have the feeling we are so close 😉
Thanks for yor support! Really appreciate!
Hi @backflash
This was not the only change in the code. Did you also switched between "Normal Order" and "Mixed Order" in the IF statement?
IF (
MaxQty < 3,
"Sample Order",
IF ( MinQty >= 3, "Normal Order", "Mixed Order" )
)
Hi @tamerj1 ,
yes, I changed this as well.
And as it always gives me 'Mixed order' for all orders, I assume that it is doing the else case in both IF clauses which would mean neither is Max-Quantity smaller 3 nor is Min-Quantity bigger than or equal to 3 - which does not make any sense, or?
@backflash
After confirming the data type please change the RETURN expreession for debugging puposses:
RETURN
RCOUNTROWS ( 'order_lines' )
RETURN
MaxQty
RETURN
MinQty
Please check if anything wrong with the resulted values and let me know. Thank you
@tamerj1I created a new table with
NewTable =
CALCULATETABLE (
'order_lines',
ALLEXCEPT ( 'order_lines', 'order_lines'[order-no] )
And I can see that the order-no are still multiple avaialble in each row. So that means it is not grouped by order-no, right?
@backflash
Yes that is strange. However try to replace with this code
Order Type =
VAR T1 =
FILTER (
'order_lines',
'order_lines'[order-no] = EARLIER ( 'order_lines'[order-no] )
)
VAR MaxQty =
MAXX ( 'T1', 'order_lines'[Quantity] )
VAR MinQty =
MINX ( 'T1', 'order_lines'[Quantity] )
VAR Result =
IF (
MaxQty < 3,
"Sample Order",
IF ( MinQty >= 3, "Normal Order", "Mixed Order" )
)
RETURN
Result
thanks for this new option.
But now it says:
A single value for column 'order-no' in table 'order-lines' cannot be determined. This can happen when a measure formula refers to a column that contain many values without specifying an aggregation such as min, max, count or sum to get a single result.
@tamerj1a calculated columns as you suggested in the first version.
should I change this into a measure completely?
No Just wanted to make sure you are using a calculated column no a measure
Hi@tamerj1 ,
sadly I cannot share the file as too much customer data is included - I would have to remove all irrelevant data and mask the remaining data - seems huge😶🌫️
But your idea with MINX and MAXX is was a very good hint - what I did instead was to write it as a SQL statement that does exactly the same thing - but this way I was able to make sure it groups by column 'order-no' by 'GROUP BY' statement at the end of my SQL query.
So even if we could not make it happen via DAX, you helped me with the logic to use MIN and MAX values to differentiate between a sample order and a normal order and identify the mixed orders as all that does not match the </> expression.
I'am now interested to understand why it did not while ir should. It worked find on my sample file. How does you data model look like?
Hi@tamerj1
ok, I can understand that 😉
This is my model:
In the beginning, I only had order_lines --> to get some useful information that represents the whole order to another level, I created a new table 'order' where I grabbed information from order_lines table like 'order-no' and some other information that is valid for the whole order, not only for the order_lines individually and created a relationship between those two tables.
this is why I still got 'customer' linked to order_lines instead of 'order' what would be the better way.
Hope this might answer you why the DAX did not work - but same logik in a SQL via DirectQuery worked fine.
at first, the first one you suggested:
Auftrags-Typ =
VAR T1 = CALCULATETABLE(Auftragszeilen_flach, ALLEXCEPT(Auftragszeilen_flach, 'Auftragszeilen_flach'[Auftrgs-Nr]))
VAR MaxQty =
MAXX ( T1, Auftragszeilen_flach[Menge])
VAR MinQty =
MINX ( T1, 'Auftragszeilen_flach'[Menge])
VAR Result =
IF (
MaxQty <= 3,
"Sample Order",
IF ( MinQty > 3, "Normal Order", "xx Order" )
)
RETURN
Result
this is where I got 'xx Order' as results for each row....
Auftragszeilen_flach[Auftrgs-Nr] = order_lines[order-no] in my file
Auftragszeilen_flach[Menge] = order_lines[quantity] in my file
Then, when I recognized, that T1 is not grouped by order-no (I created a table with only the calculate-table-part) I tried your second option with FILTER instead of CALCULATECOLUMNS
- but with FILTER it says that it is not possible as a single value cannot be determined for Auftragszeilen_flach[Auftrgs-Nr] (=order_lines[order-no])
Auftrags-Typ =
VAR T1 = FILTER (
Auftragszeilen_flach,
Auftragszeilen_flach[Auftrgs-Nr] = EARLIER ( Auftragszeilen_flach[Auftrgs-Nr])
)
VAR MaxQty =
MAXX ( T1, Auftragszeilen_flach[Menge])
VAR MinQty =
MINX ( T1, 'Auftragszeilen_flach'[Menge])
VAR Result =
IF (
MaxQty <= 3,
"Sample Order",
IF ( MinQty > 3, "Normal Order", "xx Order" )
)
RETURN
Result
@backflash
Yes because you are creating the calculated column in another table ( order no. ) table which is a dimention table with different granularity. The code is meant to be created in the Auftragszeilen_flach table not the Auftragszeilen table.
Not sure abouth the structure of the Auftragszeilen table but most probably if we change the code we can create the column in this table as well.
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 |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
50 | |
46 | |
16 | |
12 |