Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Need Help With Dax Table Query

I have the below Dax measure that produces a list of parent suppliers with the sum of the spend on them from a fact table.

 

Parent Supl Spend = var parents = SUMMARIZE('Export','Export'[ParentSupplierName],'Export'[LeaderName],"Spend",SUM('Export'[Spend Amount]))
 
Return parents
 
The lines returned from one supplier look like this:
 

 

I would like to extract from this output ONLY the line with the GREATEST spend.   How would I do that?

 

Mark Janecek

PTP Metrics Analyst 

Johnson Controls

 

Need help with DAX
Need help.
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

     

    I suggest you to try this measure.

     

    GROUP =
CALCULATE (
    MAX ( 'Parent Supl Spend'[Spend] ),
    ALLEXCEPT ( 'Parent Supl Spend', 'Parent Supl Spend'[ParentSupplierName] )
)

     

     

    If you want to get max spend for each user in calcualted table, please try this code.

    Parent Supl Spend =
VAR _Parent =
    SUMMARIZE (
        'Export',
        'Export'[ParentSupplierName],
        'Export'[LeaderName],
        "Spend", SUM ( 'Export'[Spend Amount] )
    )
VAR _MAX =
    SUMMARIZE (
        FILTER (
            _Parent,
            VAR _MaxSpend =
                MAXX (
                    FILTER ( _Parent, [ParentSupplierName] = EARLIER ( [ParentSupplierName] ) ),
                    [Spend]
                )
            RETURN
                [Spend] = _MaxSpend
        ),
        [ParentSupplierName],
        [LeaderName],
        [Spend]
    )
RETURN
    _MAX

     

    Best Regards,
    Rico Zhou

     

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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

     

    I suggest you to try this measure.

     

    GROUP =
CALCULATE (
    MAX ( 'Parent Supl Spend'[Spend] ),
    ALLEXCEPT ( 'Parent Supl Spend', 'Parent Supl Spend'[ParentSupplierName] )
)

     

     

    If you want to get max spend for each user in calcualted table, please try this code.

    Parent Supl Spend =
VAR _Parent =
    SUMMARIZE (
        'Export',
        'Export'[ParentSupplierName],
        'Export'[LeaderName],
        "Spend", SUM ( 'Export'[Spend Amount] )
    )
VAR _MAX =
    SUMMARIZE (
        FILTER (
            _Parent,
            VAR _MaxSpend =
                MAXX (
                    FILTER ( _Parent, [ParentSupplierName] = EARLIER ( [ParentSupplierName] ) ),
                    [Spend]
                )
            RETURN
                [Spend] = _MaxSpend
        ),
        [ParentSupplierName],
        [LeaderName],
        [Spend]
    )
RETURN
    _MAX

     

    Best Regards,
    Rico Zhou

     

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

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi Anonymous 

    please use

    Parent Supl Spend =
TOPN (
    1,
    SUMMARIZE (
        'Export',
        'Export'[ParentSupplierName],
        'Export'[LeaderName],
        "Spend", SUM ( 'Export'[Spend Amount] )
    ),
    [Spend]
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thank you for the help!

    The below code First returns 2 tables of supplier keys that have invoiced in one system or another.(aribainvs & fs2invs).

     

    Then it returns a table of a union of those 2 results (_allinvs).  

     

    I am trying to take the union and return every entry from the table named Supplier lookup that does NOT have a Supplier Key in the Union.  

     

    My CALCULATETABLE Function is saying the _allinvs table variable can't be found.  I don't understand why?

     

    EVALUATE
    /*Retruns table of supplier keys that invoiced with Ariba*/
    VAR aribainvs =
        SUMMARIZE (
            FILTER (
                'Onboarding Report',
                NOT (
                    ISBLANK ( 'Onboarding Report'[Invoice Submit Date] )
                )
            ),
            'Onboarding Report'[SupplierKey]
        ) 
        
        /*Returns Table of supplier keys that Invoices with FS2*/
    VAR fs2invs =
        DISTINCT ( 'FS2 Invoices'[SupplierKey] )
    VAR _allinvs =
        UNION (
            aribainvs,
            fs2invs
        )
    VAR noinvs =
        CALCULATETABLE (
            _allinvs,
            _allinvs[SupplierKey] <> 'Supplier Lookup'[SupplierKey]
        )
    RETURN
        _allinvs