Need help with the count of missing categories

[RichOB]

Hi, all dogs in my kennel need to have the Parvo, Rabies, and Distemper vaccinations. The Dog_Name column is all of the dogs currently in the kennels. The Requirements column is the vaccination the dog has got. What measure will show me: 

Toby = missing Distemper 

Spot = not missing any

Max = missing Rabies and Parvo

I really only need to show them as numbers, so in this scenario each vaccination has 1 dog missing it.

Dog_Name	Requirements
Toby	Parvo
Spot	Parvo
Toby	Rabies
Spot	Rabies
Max	Distemper
Spot	Distemper

Thanks!

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[RichOB]

Hi @Ashish_Mathur , thanks for your reply.

How could I arrange this so the Vaccines are in the left column and the number of missing rows are in the right column like the photo below, please? When I do this it changes the values to 0.



Thanks

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[danextian]

Hi @RichOB 

Create a relate dimensions table for the requirements and then try the following measure:

Missing Vaccine = 
SUMX (
    SUMMARIZECOLUMNS (
        'Table'[Dog_Name],
        Requirements[Requirements],
        "@count",
            CALCULATE (
                COUNTROWS ( Requirements ),
                KEEPFILTERS (
                    NOT Requirements[Requirements] IN VALUES ( 'Table'[Requirements] )
                )
            )
    ),
    [@count]
)

Please see the attached pbix for the details.

[Kedar_Pande]

@RichOB 

Create a table with the required vaccinations:

Vaccinations = {"Parvo", "Rabies", "Distemper"}

Create the measure

Missing Vaccinations = 
VAR RequiredVaccinations = {"Parvo", "Rabies", "Distemper"}
VAR DogVaccinations = DISTINCT('Table'[Requirements])
VAR MissingVaccinations = EXCEPT(RequiredVaccinations, DogVaccinations)
RETURN COUNTROWS(MissingVaccinations)

[RichOB]

hi @Kedar_Pande Thanks for your reply. How can this count the blank spaces per vaccine? It's the number of outstanding vaccines I need to know. Thanks