DAX formula to get the highest (Max) in the lower hierarchy

[Anonymous]

I have a hierarchy of Account and Order Number (an Account can have multiple orders). Each order has Liquid and Dry elements.

I am counting the number Liquids from each order. I want to get the max number of Liquid in any given order for an account in my table. Something like below.

My data is as follows

Order-ID	Order Number	Account	Item	Qty
1	100	ABC	A	2
2	100	ABC	B	4
3	100	ABC	C	1
4	200	XYZ	A	1
5	200	XYZ	C	1
6	300	LMO	B	2
7	300	LMO	C	1
8	400	ABC	A	1
9	400	ABC	B	4
10	400	ABC	C	2
11	400	ABC	D	1
12	500	LMO	E	3

Order-ID	Outgoing_ID	Order Number	Item	Liquid
1	1	100	A	Y
1	2	100	A	Y
2	3	100	B	N
2	4	100	B	N
2	5	100	B	N
2	6	100	B	N
3	7	100	C	Y
4	8	200	A	Y
5	9	200	C	Y
6	10	300	B	N
6	11	300	B	N
7	12	300	C	Y
8	13	400	A	Y
9	14	400	B	N
9	15	400	B	N
9	16	400	B	N
9	17	400	B	N
10	18	400	C	Y
10	19	400	C	Y
11	20	400	D	N
12	21	500	E	N
12	22	500	E	N
12	23	500	E	N

As seen from the data, Account ABC has 4 'N' in order 100 and 5'N' in order 400. I want my measure to display this 5 for ABC and 3 for LMO using the same logic.

Can someone help me come up with DAX measure formula to get the desired output

[LivioLanzo · Accepted solution]

Hi @Anonymous

I hav built the below model and added the below measure. Let me know if you need me to share the file.

Max_Liquid =
CALCULATE (
    MAXX (
        SUMMARIZE (
            Liquidities,
            Orders[Account],
            Orders[Order Number]
        ),
        [TotalLiquid]
    ),
    ALL ( Orders[Order Number] )
)

where 

[TotalLiquid] =  CALCULATE( COUNTROWS( Liquidities ), Liquidities[Liquid] = "N" )

[Anonymous]

Works just the way I want. Thank you so much. Was stuck on this for quite a while.