cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sushil_Kumar777 Frequent Visitor
Frequent Visitor

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

Re: Row Comparison

Look into using NATURALINNERJOIN or GENERATE


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

Proud to be a Datanaut!


Community Support Team
Community Support Team

Re: Row Comparison

@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.

Sushil_Kumar777 Frequent Visitor
Frequent Visitor

Re: Row Comparison

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

Highlighted
Sushil_Kumar777 Frequent Visitor
Frequent Visitor

Re: Row Comparison

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.