Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
CarlosPereira
Frequent Visitor

UNION Filtered Data and then RANKX

Hello,

 

I'm trying to create a rank between data from 2 tables

 

table 1

CompanyCategoryvalue
AYY5
AZZ1
BYY3
BZZ4

 

table 2

CompanyTypeCategoryValue
C1YY1
C1ZZ2
D1YY4
D1ZZ3
E2ZZ2
F2ZZ3

 

On a page I have filters for both of them, for example, on table 1 I filtered to show only company A and on table 2 I filtered to show only Type 1.

 

What I need is to show the rank of company A, using the value, of all categories, and rank with the values of table 2. The result should be:

CompanyCategoryValueRank
AYY53
AZZ11

 

How can I do this?

 

Thank you

1 ACCEPTED SOLUTION

Hi @CarlosPereira 

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 .

vxiaotang_0-1641868514653.pngvxiaotang_1-1641868532438.png

 

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.

View solution in original post

7 REPLIES 7
v-xiaotang
Community Support
Community Support

Hi @CarlosPereira 

vxiaotang_0-1641792309707.png

 

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.

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

Hi @CarlosPereira 

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 .

vxiaotang_0-1641868514653.pngvxiaotang_1-1641868532438.png

 

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.

CarlosPereira
Frequent Visitor

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
VahidDM
Super User
Super User

Hi @CarlosPereira 

 

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/

 

 

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

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors