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.
Dear community,
hope you are fine.
I'm trying to segregate rows with common Year but with different values on Priority scale, to use on another table.
The rule should be: for a specific ID, if a certain Year has just Priority #1, then average its prices; if the Year has both Priorities #1 and #4, then exclude Priority #1 rows, and pick the price of Priority #4; if the Year just has Priority #4, then pick the price of Priority #4.
So my data sample is something like this below:
ID | YEAR | PRIORITY | PRICE |
1 | 2023 | 1 | 5 |
1 | 2023 | 1 | 3 |
1 | 2024 | 1 | 1 |
1 | 2024 | 1 | 2 |
1 | 2024 | 4 | 1 |
1 | 2025 | 4 | 4 |
1 | 2026 | 4 | 1 |
And the desired outcome would be:
ID | YEAR | PRIORITY | PRICE |
1 | 2023 | 1 | 4 |
1 | 2024 | 4 | 1 |
1 | 2025 | 4 | 4 |
1 | 2026 | 4 | 1 |
Note: There will be several different IDs in the data model.
Any help, please?
Thanks a lot!
Regards
Solved! Go to Solution.
Hello @Anonymous
The solution is attached for your reference.
I have created couple of measures and a table using dax based on the condition when
-Priority = 4 -> keep the price same for the year.
-Prioirty = 1 -> average the price for the year.
https://drive.google.com/file/d/17Vjx0hbq_wJltpGuZKuAhkmZ_QQv0Gn4/view?usp=sharing
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and I would love to see your like.
Regards
Kumail Raza
Hello @Anonymous
The solution is attached for your reference.
I have created couple of measures and a table using dax based on the condition when
-Priority = 4 -> keep the price same for the year.
-Prioirty = 1 -> average the price for the year.
https://drive.google.com/file/d/17Vjx0hbq_wJltpGuZKuAhkmZ_QQv0Gn4/view?usp=sharing
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and I would love to see your like.
Regards
Kumail Raza
Hello @Anonymous
The solution is attached for your reference.
I have created couple of measures and a table using dax based on the condition when
-Priority = 4 -> keep the price same for the year.
-Prioirty = 1 -> average the price for the year.
https://drive.google.com/file/d/17Vjx0hbq_wJltpGuZKuAhkmZ_QQv0Gn4/view?usp=sharing
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and I would love to see your like.
Regards
Kumail Raza
Hello @Anonymous
If you could provide sample .pbix file, that would greatly help writing you a solution quickly.
Regards
Kumail Raza
Hi @Kumail! thanks for your interest!
Unforntunately this is a company reserved data model, I can not share the file, but the tables that I posted have the data I need.
Cheers
@Anonymous You can just send the .pbix with the current table and I will get back to you.
Regards
Kumail Raza
@Anonymous , Try a measure like
averageX(summarize(Table, Table[year], "_1", calculate(average(Table[PRICE]), Table[PRIORITY] =1), "_2", calculate(average(Table[PRICE]), Table[PRIORITY] =4)), if(not(isblank(_2)) && not(isblank(_1)), [_2],if(not(isblank(_2)),[_2],[_1] )))
take max for PRIORITY
Hi @amitchandak! thanks for your tip.
First, I forget to add the ID column in the sample tables, which may make any differences.
Don't know if it's related with that point that I mentioned above, but unfortunately your solution didn't work.
Do you have any clue on that?
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 |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |