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.

smpa01's avatar
smpa01
Community Champion
4 years ago

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

 

 

 

 

 

 

 

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