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.

v-xiaotang's avatar
v-xiaotang
Community Support
4 years ago

Hi Anonymous 

 

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello,

     

    I forgot to say that there os a relationship between them in categoria, so the first line will only look for category YY on table 2 and the second line for category ZZ

    • v-xiaotang's avatar
      v-xiaotang
      Community Support
      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.