Filter a graph by Maximum of a category

[DwanYb]

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:

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:

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.

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!!

[MFelix · Accepted solution]

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.

[v-gizhi-msft · Accepted solution]

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:

Hope this helps.

Best Regards,

Giotto

[DwanYb]

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 🙂

[v-gizhi-msft]

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 

[DwanYb]

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

Thanks again.

[MFelix · Accepted solution]

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.

[DwanYb]

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
)