cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
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
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
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

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.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors