Compound DAX formula. FILTER, MAX and IF

[angelikakolacz]

Hello,

I need help. I'm looking for a DAX formula that gives me the next output: 

Account number	Invoice number	Category	Output
100465-8-2020	810000004022584	1	0
100465-8-2020	810000004022624	1	0
100465-8-2020	810000004023239	1	810000004023239
1005268-10-2020	810000006602189	1	0
1005268-10-2020	810000006602328	1	0
1005268-10-2020	810000008661501	1	810000008661501
1005268-11-2020	810000006601178	1	0
1005268-11-2020	810000006602429	1	0
1005268-11-2020	810000008660414	1	810000008660414
1005268-12-2020	810000006601199	1	0
1005268-12-2020	810000006601535	1	0
1005268-12-2020	810000008661358	1	810000008661358
1005601-7-2020	810000001131154	2	0
1005601-7-2020	810000001317190	2	0
1005601-7-2020	810000001536285	2	0
1005601-7-2020	810000010994982	2	0

In the output i want to see the MAX invoice number. It's important that output is the invoice number only when the category is 1. 

I think you should use FILTER, MAX and IF only i don't know in which order...

[Tahreem24]

@angelikakolacz First create column then measure like below:

Measure = CALCULATE(MAX(InvoiceTable[Invoice number]),ALLEXCEPT(InvoiceTable,InvoiceTable[Account number]))

Column = If(InvoiceTable[Invoice number]=[Measure],[Measure],0)

[angelikakolacz]

Hi @Tahreem24 , 

Thank you for your quick respons. I have an other problem. 
As result i want to see the MAX of invoice number when the type is the highst. I have add a new column "Count of type". And now i want to see the MAX when the count is the highest (still by account number and only if the category is 1). Can you help me? 
See below result: 

Account Number	Invoice number	Type	Categoru	Count of type	Output
100465-8-2020	810000004023239	CM	1	2	810000004023239
100465-8-2020	810000004022624	INV	1	1	0
100465-8-2020	810000004022584	CM	1	2	0
1005268-10-2020	810000008661501	CM	1	1	0
1005268-10-2020	810000006602189	INV	1	2	810000006602189
1005268-10-2020	810000006602328	INV	1	2	0
1005268-11-2020	810000008660414	CM	1	2	810000008660414
1005268-11-2020	810000006601178	CM	1	2	0
1005268-11-2020	810000006602429	INV	1	1	0
1005268-12-2020	810000008661358	CM	1	2	810000008661358
1005268-12-2020	810000006601535	CM	1	2	0
1005268-12-2020	810000006601199	INV	1	1	0
1005601-7-2020	810000010994982	CM	0	3	0
1005601-7-2020	810000001536285	CM	0	3	0
1005601-7-2020	810000001317190	CM	0	3	0
1005601-7-2020	810000001131154	INV	0	1	0

[v-yinliw-msft · Accepted solution]

Hi @angelikakolacz ,

You can try this method:

Add a index column first.

New measure:

Count =
CALCULATE (
    COUNTROWS ( InvoiceTable ),
    FILTER (
        ALL ( InvoiceTable ),
        [Account Number] = MAX ( 'InvoiceTable'[Account Number] )
            && [Type] = MAX ( 'InvoiceTable'[Type] )
    )
)

Output =
VAR _a =
    CALCULATE (
        MAX ( InvoiceTable[Invoice number] ),
        FILTER (
            ALL ( 'InvoiceTable' ),
            [Account Number] = MAX ( 'InvoiceTable'[Account Number] )
                && [Categoru] = 1
        )
    )
RETURN
    IF ( MAX ( 'InvoiceTable'[Invoice number] ) = _a, _a, 0 )

The table looks like:

Is this what you expect?

Hope this helps you.

Here is the PBIX file.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lukiz84]

Hi,

Output =
   CALCULATE(
      MAX(table[Invoice Number]),
      table[Category] = 1
   )