cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Urgent : group filter and use case

I'm fairly new to DAX and tried to find a solution for this problem but i'm not able to and this is urgent now.

 

I have a column with owner names and another with hospital names. An owner can have 1 or more hospitals to its name but a hospital will have only 1 owner. Based on Hospitals' condition they're graded as A, B and C. Now if all the hospitals under an owner are graded A then owner's grade is A. If any of the hospital's grade is B and others are A, then owner's grade will be B. If any of hospital's grade is C and others are A and\or B then owner grade will be C.

 

Need to create a calculated column to showcase this in dashboard grouped by Owner.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Urgent : group filter and use case

@Anonymous Here is what I came up with based on your description. First, I added a calculated column to transform the A,B,C to numeric values. Then created a measure to return the highest one which should correspond to their worst score. Table on left is the raw sample, table on right is overall score - code is below.

 

Top Owner.PNG


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

3 REPLIES 3
Highlighted
Super User III
Super User III

Re: Urgent : group filter and use case

@Anonymous Here is what I came up with based on your description. First, I added a calculated column to transform the A,B,C to numeric values. Then created a measure to return the highest one which should correspond to their worst score. Table on left is the raw sample, table on right is overall score - code is below.

 

Top Owner.PNG


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

Anonymous
Not applicable

Re: Urgent : group filter and use case

I wasn't able to come up with that logic. This worked like a charm. Thanks.

S184019 Regular Visitor
Regular Visitor

Re: Urgent : group filter and use case

Hey Seth @Seth_C_Bauer,

Do you know of a way to do this dynamically without enumerating the values.  For an example, let's assume we want to visualize metrics for an individual.  The individual is arbitrarily assigned to a group.  I am trying to conditionally format my visualizations (tables) based upon the group.  So if group name is ABC then change table header to blue, DEF then green, GHI then red and so on. 

 

I can manually group them using SQL before PBI, but then every time the end user decides to change the individuals within the group, I would have to reassign the enumerations in the switch function. Ideally a dynamic method for identifying a group would be ideal.  

 

Your currently proposed solution would be the DAX (switch) equivalent to sql's:

Case when Group = 'ABC' then 1

         when Group = 'DEF' then 2

         when Group = 'GHI' then 3

         when Group = 'JKL' then 4... end.

 

Whereas I am looking more to do something like the following but using DAX

 

Case when Group = <<First unique group>> then 1

        when Group = <<Second unique group>> then 2

        when Group = <<Third unique group>> then 3

        when Group = <<Fourth unique group>> then 4 ... end.

 

Then set a conditional formatting rule in a table wherein 1=blue, 2=Green and so on.  The benefit of doing it this way, is I don't have to enumerate the group, I can just let the system decide how to enumerate. 

 

Thanks in advance, 

 

Me. 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors