Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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:

IDYEARPRIORITYPRICE
1202315
1202313
1202411
1202412
1202441
1202544
1202641

 

And the desired outcome would be:

IDYEARPRIORITYPRICE
1202314
1202441
1202544
1202641

 

Note: There will be several different IDs in the data model.

 

 

Any help, please?

 

Thanks a lot!

 

Regards

1 ACCEPTED SOLUTION
Kumail
Post Prodigy
Post Prodigy

Hello @Anonymous 

 

The solution is attached for your reference.

Kumail_0-1624465458277.png

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.

Kumail_1-1624465596426.pngKumail_2-1624465637953.pngKumail_3-1624465664407.pngKumail_4-1624465680998.png

 

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

 

View solution in original post

8 REPLIES 8
Kumail
Post Prodigy
Post Prodigy

Hello @Anonymous 

 

The solution is attached for your reference.

Kumail_0-1624465458277.png

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.

Kumail_1-1624465596426.pngKumail_2-1624465637953.pngKumail_3-1624465664407.pngKumail_4-1624465680998.png

 

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
Not applicable

@Kumail Thanks for your help!

I can take as good your suggestion. 

 

Best regards

Kumail
Post Prodigy
Post Prodigy

Hello @Anonymous 

 

The solution is attached for your reference.

Kumail_0-1624465458277.png

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.

Kumail_1-1624465596426.pngKumail_2-1624465637953.pngKumail_3-1624465664407.pngKumail_4-1624465680998.png

 

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

 

Kumail
Post Prodigy
Post Prodigy

Hello @Anonymous 

 

If you could provide sample .pbix file, that would greatly help writing you a solution quickly.

 

Regards

Kumail Raza

Anonymous
Not applicable

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

amitchandak
Super User
Super User

@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

Anonymous
Not applicable

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.