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'[ParentSup...
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.

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