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

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

@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

@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

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. 

Anonymous
Not applicable

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

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.