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 numberInvoice numberCategoryOutput
100465-8-202081000000402258410
100465-8-202081000000402262410
100465-8-20208100000040232391810000004023239
1005268-10-202081000000660218910
1005268-10-202081000000660232810
1005268-10-20208100000086615011810000008661501
1005268-11-202081000000660117810
1005268-11-202081000000660242910
1005268-11-20208100000086604141810000008660414
1005268-12-202081000000660119910
1005268-12-202081000000660153510
1005268-12-20208100000086613581810000008661358
1005601-7-202081000000113115420
1005601-7-202081000000131719020
1005601-7-202081000000153628520
1005601-7-202081000001099498220

 

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

 

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.

4 Replies

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

  • lukiz84's avatar
    lukiz84
    Icon for Memorable Member rankMemorable Member
    3 years ago

    Hi,

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