Forum Discussion

angelikakolacz's avatar
angelikakolacz
Icon for Helper I rankHelper I
3 years ago
Solved

Compound DAX formula. FILTER, MAX and IF

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 ...
Need Help
  • v-yinliw-msft's avatar
    v-yinliw-msft
    3 years ago

    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.

Tahreem24's avatar
Tahreem24
Icon for Super User rankSuper User
3 years ago

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's avatar
angelikakolacz
Icon for Helper I rankHelper I
3 years ago

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 NumberInvoice numberTypeCategoruCount of typeOutput
100465-8-2020810000004023239CM12810000004023239
100465-8-2020810000004022624INV110
100465-8-2020810000004022584CM120
1005268-10-2020810000008661501CM110
1005268-10-2020810000006602189INV12810000006602189
1005268-10-2020810000006602328INV120
1005268-11-2020810000008660414CM12810000008660414
1005268-11-2020810000006601178CM120
1005268-11-2020810000006602429INV110
1005268-12-2020810000008661358CM12810000008661358
1005268-12-2020810000006601535CM120
1005268-12-2020810000006601199INV110
1005601-7-2020810000010994982CM030
1005601-7-2020810000001536285CM030
1005601-7-2020810000001317190CM030
1005601-7-2020810000001131154INV010



  • v-yinliw-msft's avatar
    v-yinliw-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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.