topic Re: Filtering Table Values in DAX Commands and Tips

Filtering Table Values

Anonymous — Wed, 05 Jan 2022 10:34:01 GMT

I wish to create a Result Table from the Data Table as seen in the attached screenshot. The Result Table must be such that it will show only those values in the three columns which are corresponding to the Maximum of Sum of Order Value.

It will be helpful if we can achieve this functionality.

Thanks

Re: Filtering Table Values

amitchandak — Wed, 05 Jan 2022 10:28:28 GMT

Anonymous , Based on what I got, a measure to be used with GUID, LPGU in visual

sumx(summarize(Table, Table[GUID], Table[LPGU], "_sum", sum(Table[Order Value] ) ), [_sum])

Re: Filtering Table Values

Anonymous — Wed, 05 Jan 2022 10:31:10 GMT

amitchandak I'll check now and get back soon. Thanks

Re: Filtering Table Values

Anonymous — Wed, 05 Jan 2022 10:47:16 GMT

amitchandak

Seems that doesn't work as it still fetches multiple values of LPGU, instead it must fetch single values of GUID, LPGU corresponding to Max Sum of Order Value as shown in the Result Table. Also I need to create Result Table as Data Table(not visual table).

Re: Filtering Table Values

amitchandak — Wed, 05 Jan 2022 12:09:56 GMT

Anonymous , Try like

sumx(summarize(Table, Table[GUID], "_sum", sum(Table[Order Value] ) ), [_sum])

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: Filtering Table Values

Anonymous — Wed, 05 Jan 2022 12:33:20 GMT

amitchandak This too doesn't work and I still can see multiple values of LPGU, GUID and Order Value.

It needs to go like Result Table Must show filtered GUID, LPGU and Maximum of Sum of Order Value and that Sum of Order Value must be done by LPGU.

Unfortunately, I can't present the sample data. You Can take data from the screenshot that I attached. Also keeping the data table here.

GUID	LPGU	Order Value
G21	KRTG	4
G21	KRVC	3
G21	KRTG	6
G21	KRVC	3
G21	KRPP	5
G65	KPIC	1
G65	KPIC	4
G65	KKRD	6
G65	GHCI	7
G65	GHCI	5
G76	TTCG	5
G76	TTRS	1
G76	TTDS	8
G76	TTCG	5

Re: Filtering Table Values

smpa01 — Wed, 05 Jan 2022 14:50:41 GMT

Anonymous you can write a measure like this

_Measure = CALCULATE ( CALCULATE ( SUM ( 'Table'[Order Value] ), ALLEXCEPT ( 'Table', 'Table'[GUID], 'Table'[LPGU] ) ), KEEPFILTERS ( TOPN ( 1, FILTER ( ALLSELECTED ( 'Table' ), 'Table'[GUID] = MAX ( 'Table'[GUID] ) ), CALCULATE ( SUM ( 'Table'[Order Value] ), ALLEXCEPT ( 'Table', 'Table'[GUID], 'Table'[LPGU] ) ), DESC ) ) )

Re: Filtering Table Values

Anonymous — Wed, 05 Jan 2022 15:07:21 GMT

smpa01 This looks good. Can we implement this as Calculated Table with all 3 columns?

Re: Filtering Table Values

AlexisOlson — Wed, 05 Jan 2022 15:48:45 GMT

Assuming you have a measure for the sum of Order Value

SumOrderValue = SUM ( Table1[Order Value] )

then we can write a measure for the top LPGU order fairly neatly:

Top LPGU Order = VAR TopLPGU = MAXX ( TOPN ( 1, ALLSELECTED ( Table1[LPGU] ), [SumOrderValue] ), Table1[LPGU] ) RETURN CALCULATE ( [SumOrderValue], KEEPFILTERS ( Table1[LPGU] = TopLPGU ) )

Edit: You can easily make this into a calculated table as follows:

SUMMARIZECOLUMNS ( Table1[GUID], Table1[LPGU], "Order Value", [Top LPGU Order] )

Re: Filtering Table Values

smpa01 — Wed, 05 Jan 2022 15:37:42 GMT

Anonymous this will be table expression

Table 2 = var _grp = GROUPBY('Table','Table'[GUID],'Table'[LPGU],"subTotal by GUIDLPGU",SUMX(CURRENTGROUP(),'Table'[Order Value])) --var _filt = TOPN(1, FILTER(_grp,var _guid = [GUID] return [GUID]=_guid),[x],DESC) var _rank = SUMMARIZE(FILTER(ADDCOLUMNS(_grp, "rank", RANKX(FILTER(_grp,[GUID]=EARLIER([GUID])),[subTotal by GUIDLPGU],,DESC)),[rank]=1),[GUID],[LPGU],[subTotal by GUIDLPGU]) return _rank

AlexisOlson in a table expression is it possible to ask TOPN to return TOPN(1) based on a partiton like in RANKX

RANKX(FILTER(_grp,[GUID]=EARLIER([GUID])

I tried but failed.

Re: Filtering Table Values

AlexisOlson — Wed, 05 Jan 2022 16:01:04 GMT

I'm not quite sure what you mean but you can simplify your _rank variable to be:

FILTER ( _grp, RANKX ( FILTER ( _grp, [GUID] = EARLIER ( Table1[GUID] ) ), [subTotal by GUIDLPGU] ) = 1 )

or like this

TOPN ( 1, _grp, RANKX ( FILTER ( _grp, [GUID] = EARLIER ( Table1[GUID] ) ), [subTotal by GUIDLPGU] ), ASC )

Re: Filtering Table Values

smpa01 — Wed, 05 Jan 2022 16:00:09 GMT

AlexisOlson Thanks. I was not asking you about RANKX. I was wondering if it is possible to pass on a partitoned table in TOPN like I did in RANKX?

Can I pass on a similar table like following in TOPN, so that TOPN (1) gives me thre result for that partiton?

RANKX ( FILTER ( _grp, [GUID] = EARLIER ( Table1[GUID] ) )

Re: Filtering Table Values

AlexisOlson — Wed, 05 Jan 2022 16:16:37 GMT

Sorry, I'm still not sure I understand what you mean by "pass a partitioned table".

If you try to write this

TOPN ( 1, FILTER ( _grp, [GUID] = EARLIER ( Table1[GUID] ) ), [...] )

it just doesn't make any sense because there's no earlier row context to refer to.