Forum Discussion
Segregating two outcomes based on a distinct value
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
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
8 Replies
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
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?
Hello Anonymous
If you could provide sample .pbix file, that would greatly help writing you a solution quickly.
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
