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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DwanYb
Regular Visitor

Filter a graph by Maximum of a category

Hi everyone,

I hope you guys are staying safe.

 

I am currently learning power bi, and stuck in the middle of a test project I created.

I created a database, with a list of people, organized by groups (GR 1-4) and categories (employee, instructor, manager). The "People" table looks like this:

Capture2.PNG

 

 

 

 

 

 

 

 

 

I defined times slots (associated to dates in another table), and everyone gave me their availabilities for a meeting on each particular time slot. Here an example or the "Availability" table:

Capture3.PNG

 

 

 

 

 

 

 

 

My goal is to find which time slot is best for our meeting. The meeting must be set at a time where there is at least an instructor, all the managers of the group. Then will find the date with the maximum of employees.

Capture.PNG

 

 

 

 

 

 

I would like to show on this table the count of employees, managers and instructors.

I would like to add filters, show only the slots where:

  • At least one instructor is here (in this case, there is only one anyway...)
  • All the managers are available

For the instructors, I created a measure 

 

CountInstructor = 
CALCULATE(DISTINCTCOUNT(Availability[Email Address]),
People[Category] = "Instructor")

 

And set the filter as: CountInstructor is not blank. The slot SL 4 disappears. Which is great

 

I have two questions:

  • Would you do that, or is there a better way?
  • For the manager, how to create a measure/filter to show only the slots when all the managers are available? I know that I can use the same kind of filters with CountManager = 2, but I would like the filter to stay dynamic, using a Maximum that could be 2 or another number depending on the cases.

Let me know if my explanation is clear enough, 

And thank you in advance!!

 

2 ACCEPTED SOLUTIONS
MFelix
Super User
Super User

Hi @DwanYb ,

 

For the managers you can do a dinamyc measure making something similar to this:

CountManager =
IF (
    CALCULATE (
        DISTINCTCOUNT ( Availability[Email Address] ),
        People[Category] = "Manager"
    )
        = CALCULATE (
            DISTINCTCOUNT ( Availability[Email Address] ),
            ALLSELECTED ( Table[GroupKey] ),
            People[Category] = "Manager"
        ),
    1,
    0
)

 

Then filter by value 1 when every managers from the groups selected are available.

 

You can change the 1 and 0 for whatever values you want.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

v-gizhi-msft
Community Support
Community Support

Hi,

 

Please create a What If parameter first.

Then try this measure:

Check = 
VAR DIstinctCount_of_Manager_per_SlotKey =
    CALCULATE (
        DISTINCTCOUNT ( Slot[Email Address] ),
        FILTER (
            Slot,
            Slot[Email Address]
                = CALCULATE (
                    MAX ( People[Email Address] ),
                    FILTER ( People, People[Category] = "Manager" )
                )
        )
    )
RETURN
    IF ( DIstinctCount_of_Manager_per_SlotKey = Parameter[Parameter Value], 1, 0 )

When select on value in slicer, it shows:

12.PNG

Hope this helps.

 

Best Regards,

Giotto

View solution in original post

6 REPLIES 6
v-gizhi-msft
Community Support
Community Support

Hi,

 

Please create a What If parameter first.

Then try this measure:

Check = 
VAR DIstinctCount_of_Manager_per_SlotKey =
    CALCULATE (
        DISTINCTCOUNT ( Slot[Email Address] ),
        FILTER (
            Slot,
            Slot[Email Address]
                = CALCULATE (
                    MAX ( People[Email Address] ),
                    FILTER ( People, People[Category] = "Manager" )
                )
        )
    )
RETURN
    IF ( DIstinctCount_of_Manager_per_SlotKey = Parameter[Parameter Value], 1, 0 )

When select on value in slicer, it shows:

12.PNG

Hope this helps.

 

Best Regards,

Giotto

Hi @v-gizhi-msft

 

It seems to be a great solution as well. The slicer is actually very useful for my next step: to select how many "employees" I want in the meeting.

 

However, I had issues with:

 

CALCULATE (
      MAX ( People[Email Address] ),
      FILTER ( People, People[Category] = "Manager" )
)

It looks like that selects the lentgh of the email address (does it?), which will return only 1 in my case.

 

I then adapted your solution, and it seems to work well: 

 

VAR  DistinctCount_Manager_perSL_two =
    CALCULATE(
        DISTINCTCOUNT(Availability[Email Address]),
        People[Category] = "Manager"
        )
RETURN
    IF ( DistinctCount_Manager_perSL_two = Parameter[Parameter Value], 1, 0)

 

Thank you 🙂

Hi,

 

The Max(People[Email Address]) in this formula will return each row's email which category is manager in order to link to another slot table but not return the length of email address:

CALCULATE (
      MAX ( People[Email Address] ),
      FILTER ( People, People[Category] = "Manager" )
)

If my answer has help solve your issue, please mark it as a solution for others to see.
Thanks! 

 

Best Regards,

Giotto 

Alright, thanks for the explanation Giotto. I need to review what was wrong in my document while using it then. 

Thanks again.

MFelix
Super User
Super User

Hi @DwanYb ,

 

For the managers you can do a dinamyc measure making something similar to this:

CountManager =
IF (
    CALCULATE (
        DISTINCTCOUNT ( Availability[Email Address] ),
        People[Category] = "Manager"
    )
        = CALCULATE (
            DISTINCTCOUNT ( Availability[Email Address] ),
            ALLSELECTED ( Table[GroupKey] ),
            People[Category] = "Manager"
        ),
    1,
    0
)

 

Then filter by value 1 when every managers from the groups selected are available.

 

You can change the 1 and 0 for whatever values you want.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix

Thanks for your answer, it works just fine. I will need to work a bit more with the function ALLSELECTED.

I just modified a bit your solution to adapt it to my case, using People[Email Address] instead of Availability[Email Address]. But I think it's what you meant.

 

Thanks again!

 

CountManager =
IF (
    CALCULATE (
        DISTINCTCOUNT ( Availability[Email Address] ),
        People[Category] = "Manager"
    )
        = CALCULATE (
            DISTINCTCOUNT ( People[Email Address] ),
            ALLSELECTED ( Groups[GroupKey] ),
            People[Category] = "Manager"
        ),
    1,
    0
)

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors