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. 

[backflash]

@tamerj1 

you are absolutely right: when I create it in the order_lines-Table, it is working correctly and marks me every order_line if it is part of a 'normal order', 'sample order' or 'mixed order'.

I marked two posts as correct solution, as this might help someone with a similar problem to get to the correct solution without scrolling through our conversation, which was only necessary because I created it in the false table!  😉

Thanks a lot for your endurance 😄

[backflash]

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

[backflash]

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?

[tamerj1]

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

[backflash]

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

[tamerj1]

@backflash 
Did you read my last reply?