Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

UNION Filtered Data and then RANKX

Hello,   I'm trying to create a rank between data from 2 tables   table 1 Company Category value A YY 5 A ZZ 1 B YY 3 B ZZ 4   table 2 Company Type Category ...
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi Anonymous 

    Try this,

    DAX code:

    Measure = 
    var __category = SELECTEDVALUE(Table1[Category])
    var __table1 = SELECTCOLUMNS(Table1,"Company",Table1[Company],"Category",Table1[Category],"Value",Table1[value])
    var __table2 = SELECTCOLUMNS(FILTER(Table2,Table2[Category]=__category),"Company",Table2[Company],"Category",Table2[Category],"Value",Table2[Value])
    var __table3 = UNION(__table1,__table2)
    var __table4 = ADDCOLUMNS(__table3,"rank",RANKX(__table3,[Value],,ASC))
return
    MAXX(FILTER(__table4,[Company]=SELECTEDVALUE(Table1[Company])),[rank])

    This measure is designed based on the sample data and expected results you provide. If requirements change, you can adjust the measure .

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

VahidDM's avatar
VahidDM
Super User
4 years ago

Hi Anonymous 

 

Can you add more details re how you find the rank numbers in the result table?

It's not that clear!

 


Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi,

     

    It is the rank looking at the values, higher the value higher the rank.

     

     

    I was trying to do this

     

    r des =
    var tb = union(SUMMARIZE('Table 2',"category",MAX('Table 2'[category]),"company",max('Table 2'[company]),"value",sum('Table 2'[value])),
    SUMMARIZE('Table 1',"category",MAX('Table 1'[category]),"company",max('Table 1'[company]),"value",sum('Table 1'[value]))
    )
    return RANKX(tb,[value],,0)

     

    But it says that there is no value on "value"

     

    Thank you

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I don't know if it helps, but table 1 and table 2 are actually duplicated tables, so they are the same.

     

    I use table 1 to show calculate measures of the company and table 2 to calculate measures of a filtered group of companies in a type.

     

    In this case I want to show the company, category, value and the rank of the value, of that company, within the values of the companies that are filtered by a type, so for company A and category YY should calculate the rank of this value and all the values of the companies with same category and filtered type 1 and the calculated rank shoul be 3. It is the same thing for company A and category ZZ and the rank should be 1