Row Comparison

[Anonymous]

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:

PharID	Rank_Value	Price	Qty	Lenght	Intergroup	YearWeek
501351	1	2	2	250	7	201817
501489	2	4	2	240	7	201817
506173	1	10	7	12	5	201817
501372	2	8	5	8	5	201817
506983	3	6	1	6	5	201817
501371	4	4	3	4	5	201817

Desired Output:

PharID	Rank_Value	Price	Qty	Lenght	Intergroup	YearWeek	Rank_Value	Qty	Price
506173	1	10	7	12	5	201817	2	5	8
506173	1	10	7	12	5	201817	3	1	6
506173	1	10	7	12	5	201817	4	3	4
501372	2	8	5	8	5	201817	3	1	6
501372	2	8	5	8	5	201817	4	3	4
506983	3	6	1	6	5	201817	4	3	4
501371	4	4	3	4	5	201817	NULL	NULL	NULL
501351	1	2	2	250	7	201817	2	2	4
501489	2	4	2	240	7	201817	NULL	NULL	NULL

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

[v-yuta-msft]

@Anonymous ,

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.

[Anonymous]

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]

Look into using NATURALINNERJOIN or GENERATE

[Anonymous]

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