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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

smpa01's avatar
smpa01
Community Champion
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

    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
)

     

    • smpa01's avatar
      smpa01
      Community Champion
      4 years ago

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

       

      • AlexisOlson's avatar
        AlexisOlson
        Super User
        4 years ago

        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.