topic Re: Need Help With Dax Table Query in DAX Commands and Tips

Need Help With Dax Table Query

Anonymous — Tue, 30 Aug 2022 18:58:59 GMT

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

Re: Need Help With Dax Table Query

tamerj1 — Tue, 30 Aug 2022 19:24:03 GMT

Hi Anonymous

please use

Parent Supl Spend = TOPN ( 1, SUMMARIZE ( 'Export', 'Export'[ParentSupplierName], 'Export'[LeaderName], "Spend", SUM ( 'Export'[Spend Amount] ) ), [Spend] )

Re: Need Help With Dax Table Query

Anonymous — Mon, 05 Sep 2022 09:31:24 GMT

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.

Re: Need Help With Dax Table Query

Anonymous — Thu, 08 Sep 2022 19:47:39 GMT

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