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
