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
rocky09
Solution Sage
Solution Sage

Assign Value based on other Values in the Group

Here is the situation, i have below table. You see, I have groups and it has different scores.  I need to find a way to assign a value i.e. ">50" or "<50" based on values in the group.

 

Example:

 

Group A contains Scores like, 42,49,55 - in this, i need to look for any value which is greater than 50, if it is there, the entire group will be assiged a value as ">50"

 

TicketGroupScore
12453A42
12477A49
12501A55
12525B45
12549B60
12573B54
12597C62
12621C12
12645C45
12669C45
12693D80
12717E45
12741E45
12765E82
12789F45
12813F24

 

 

Result Expecting:

 

TicketGroupScoreResult
12453A42>50
12477A49>50
12501A55>50
12525B45>50
12549B60>50
12573B54>50
12597C62>50
12621C12>50
12645C45>50
12669C45>50
12693D80>50
12717E45<50
12741E45<50
12765E82>50
12789F45<50
12813F24<50

 

anyway to do this in PowerBI?

 

Thank you in advance.

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

Hey,

 

given your sample data, I do not understand your expected result for group E

 

There is a value 82 for ticket 12765, for this reason I would expect that the whole group E is marked with >50.

 

I guess this DAX statement can be used to create a calculated column:

bin Score = 
    CALCULATE(
        IF(MAX('Table1'[Score])>50,">50","<50")
        ,ALLEXCEPT('Table1',Table1[Group])
    ) 

This creates the following

Value by Group.png

 

Hope this helps

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

2 REPLIES 2
TomMartens
Super User
Super User

Hey,

 

given your sample data, I do not understand your expected result for group E

 

There is a value 82 for ticket 12765, for this reason I would expect that the whole group E is marked with >50.

 

I guess this DAX statement can be used to create a calculated column:

bin Score = 
    CALCULATE(
        IF(MAX('Table1'[Score])>50,">50","<50")
        ,ALLEXCEPT('Table1',Table1[Group])
    ) 

This creates the following

Value by Group.png

 

Hope this helps

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Perfect.

 

Thank you so much.

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.