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

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.

12 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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

      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

        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.

  • 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

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