cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sushil_Kumar777
Helper I
Helper I

Row Comparison

Hi Team,

I have below sample data and desired output and same thing i can achive in SQL but not sure with Power BI ,please need help here.

I want to compare Rank 1 with other lower rank values within that group 

example1: Rank 1 with Rank2 in Intergoup 7

exmaple2: Rank 1 with Rannk 2 ,3,4 In Intergoup 5 

                  Rank 2 with Rank 3 ,4 In Intergoup 5 

                  Rank 3 with Rank  4 In Intergoup 5 

                  Rank 4  with noting In Intergoup 5 

 

1. Can i achive this without creatieng another  extra fact table using m code ?

2. Can i achive this by using DAX in report without creating extra fact table?

 

Sample Data:

 

PharIDRank_ValuePriceQtyLenghtIntergroupYearWeek
5013511222507201817
5014892422407201817
5061731107125201817
50137228585201817
50698336165201817
50137144345201817

 

 

Desired Output:

PharIDRank_ValuePriceQtyLenghtIntergroupYearWeekRank_ValueQtyPrice
5061731107125201817258
5061731107125201817316
5061731107125201817434
50137228585201817316
50137228585201817434
50698336165201817434
50137144345201817NULLNULLNULL
5013511222507201817224
5014892422407201817NULLNULLNULL

 

 

Query which gives me desired output:

 

select a.*,b.Rank_Value,b.[Qty],b.[Price]
from [Sample Data] a left join [Sample Data] b
on a.[YearWeek]=b.[YearWeek] and a.Intergroup=b.Intergroup and a.Rank_Value< b.Rank_Value
order by a.[YearWeek],a.Intergroup,a.Rank_Value,b.Rank_Value

4 REPLIES 4
v-yuta-msft
Community Support
Community Support

@Sushil_Kumar777 ,

 

This can be achieved in dax. You may also refer to summarize(), groupby() function in dax. 

 

Community Support Team _ Jimmy Tao

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

Hi Greg/Support team,

I Couldn't ahchive using your suggestion ,please can you upload the example pbix file using my sample uploded data if possible, thanks a lot in advance.

 

 

 

Greg_Deckler
Super User IV
Super User IV

Look into using NATURALINNERJOIN or GENERATE


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks for your valuable suggestion, I will check and update the status.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.