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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

I'm trying something like this

 

Rank 2 = RANKX(
filter(
all('Table 2'[Company],'Table 2'[Type],'Table 2'[Category]),
'Table 2'[Category]=SELECTEDVALUE('Table 1'[Category]) &&
('Table 2'[Company]=SELECTEDVALUE('Table 1'[Company]) ||
'Table 2'[Type]=SELECTEDVALUE('Table 2'[Type]))),
'Table 2'[Total Value],,0)
 
but it allways returns 1 ðŸ˜•
 
anyone can help?
 
Thank you