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

[backflash]

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!

[tamerj1 · Accepted 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. 

[tamerj1 · Accepted 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. 

[backflash]

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?

[tamerj1 · Accepted solution]

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

[backflash]

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!

[tamerj1]

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

[backflash]

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?

[tamerj1]

@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

[backflash]

@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?

[tamerj1]

@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

[backflash]

@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.

[tamerj1]

@backflash 
Are you creating a calculated column or a measure?

[backflash]

@tamerj1a calculated columns as you suggested in the first version.

should I change this into a measure completely?

[tamerj1]

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

[tamerj1]

@backflash 
Can you please share sample file?

[backflash]

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.

[tamerj1]

@backflash 

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? 

[backflash]

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.

[tamerj1]

@backflash 
What exactly was the DAX code that you've used?

[backflash]

@tamerj1 

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

[tamerj1]

@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.