topic I need a DAX expression for ...where values of all related rows are true in DAX Commands and Tips

I need a DAX expression for ...where values of all related rows are true

backflash — Fri, 11 Mar 2022 10:38:49 GMT

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:

Amount Sample Orders = CALCULATE(DISTINCTCOUNT(order_lines[order-no]), KEEPFILTERS( order_lines[IsSample]=TRUE()))

- 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:

Amount of Sample Orders = CALCULATE(

DISTINCTCOUNT(order_header[order-no]),

KEEPFILTERS(order_lines[IsSample]=TRUE()))

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!

Re: I need a DAX expression for ...where values of all related rows are true

tamerj1 — Fri, 11 Mar 2022 13:09:44 GMT

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.

Re: I need a DAX expression for ...where values of all related rows are true

backflash — Fri, 11 Mar 2022 13:46:47 GMT

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?

Re: I need a DAX expression for ...where values of all related rows are true

tamerj1 — Fri, 11 Mar 2022 14:19:49 GMT

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

Re: I need a DAX expression for ...where values of all related rows are true

backflash — Mon, 14 Mar 2022 06:49:26 GMT

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!

Re: I need a DAX expression for ...where values of all related rows are true

tamerj1 — Mon, 14 Mar 2022 07:02:51 GMT

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" ) )

Re: I need a DAX expression for ...where values of all related rows are true

backflash — Mon, 14 Mar 2022 08:27:44 GMT

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?

Re: I need a DAX expression for ...where values of all related rows are true

tamerj1 — Mon, 14 Mar 2022 08:56:19 GMT

backflash
Would you please double check the data type of the QTY column. It should be of Integer type. Please also check the same in the Power Query Editor (make sure it is Whole Number)

Re: I need a DAX expression for ...where values of all related rows are true

tamerj1 — Mon, 14 Mar 2022 09:04:42 GMT

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

Re: I need a DAX expression for ...where values of all related rows are true

backflash — Mon, 14 Mar 2022 09:40:24 GMT

Hi tamerj1

all values returned are correct.

COUNTROWS(order_lines): 84.377 --> which is correct

MaxQty: 6200 --> which is really the highest quantity-value of all order-lines

MinQty: 0 --> which is also correct, because we have some 0-quantity order-lines due to the way the webshop funtioned in the past.

Not sure if this caused the trouble, I removed all the 0-quantity order-lines,and now I get for

return MinQty: 1 --> which is correct

but still:
All orders are set as 'Mixed orders'

But all values (distinctcount of order-no, MinQuantity and MaxQuantity overall) are correct!

But the values were not grouped by the order-no - but instead was the min and max quantity from all order lines that are avaialble.

So it might be the missing grouping by order-no?

Re: I need a DAX expression for ...where values of all related rows are true

backflash — Mon, 14 Mar 2022 09:43:54 GMT

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?

Re: I need a DAX expression for ...where values of all related rows are true

tamerj1 — Mon, 14 Mar 2022 09:54:35 GMT

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

Re: I need a DAX expression for ...where values of all related rows are true

backflash — Mon, 14 Mar 2022 09:56:56 GMT

tamerj1it is WholeNumber in QueryEditor and is presented as WholeNumber in data view:

Re: I need a DAX expression for ...where values of all related rows are true

tamerj1 — Mon, 14 Mar 2022 10:05:54 GMT

backflash
Did you read my last reply?

Re: I need a DAX expression for ...where values of all related rows are true

backflash — Mon, 14 Mar 2022 10:22:26 GMT

tamerj1

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.

Re: I need a DAX expression for ...where values of all related rows are true

tamerj1 — Mon, 14 Mar 2022 10:25:13 GMT

backflash
Are you creating a calculated column or a measure?

Re: I need a DAX expression for ...where values of all related rows are true

backflash — Mon, 14 Mar 2022 10:38:43 GMT

tamerj1a calculated columns as you suggested in the first version.

should I change this into a measure completely?

Re: I need a DAX expression for ...where values of all related rows are true

backflash — Mon, 14 Mar 2022 10:55:01 GMT

tamerj1when I change it to a measure it says that my EARLIER refers to an earlier row context that doesn't exist.

Seems to have a problem identifying the correct row to choose.

Re: I need a DAX expression for ...where values of all related rows are true

tamerj1 — Mon, 14 Mar 2022 10:56:46 GMT

No Just wanted to make sure you are using a calculated column no a measure

Re: I need a DAX expression for ...where values of all related rows are true

tamerj1 — Mon, 14 Mar 2022 11:04:42 GMT

backflash
Can you please share sample file?