Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
@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.
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.
Look into using NATURALINNERJOIN or GENERATE
Thanks for your valuable suggestion, I will check and update the status.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |