Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Filtering Table Values

Hello All, parry2k smpa01 AlexisOlson  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...
DAX Calculation
DAX Commands and Tips
Filter Multiple Columns
Filter Table
  • smpa01's avatar
    smpa01
    4 years ago

    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.

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

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] )