Forum Discussion

RichOB's avatar
RichOB
Icon for Post Partisan rankPost Partisan
1 year ago
Solved

Measure to highlight missing text and make it a count

Hi, all dogs in my kennel need to have the Parve, Rabies, and Distemper vaccinations. When the name is in the column, it means that the dog has them. What measure will highlight the dogs who have the missing vaccinations, please? I need to show

 

Parvo = missing 2

Rabies = missing 1

distemper = missing 1

 

Dog_NameRequirements
TobyParvo
Max 
Rex 
SpotParvo
TobyRabies
MaxRabies
RexRabies
Spot 
TobyDistemper
Max 
RexDistemper
SpotDistemper

 

Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi RichOB ,

    Based on the description, create the new measure for each vaccination to calculate the count missing.

     

    Parvo_Missing = 
VAR _Parvo = 
    CALCULATETABLE(
        VALUES('Table'[Dog_Name]),
        'Table'[Requirements] = "Parvo"
    )
VAR _Dogs = 
    VALUES('Table'[Dog_Name])
RETURN 
    COUNTROWS(EXCEPT(_Dogs, _Parvo))

     

     

    Rabies Missing = 
VAR _Rabies = 
    CALCULATETABLE(
        VALUES('Table'[Dog_Name]),
        'Table'[Requirements] = "Rabies"
    )
VAR _Dogs = 
    VALUES('Table'[Dog_Name])
RETURN 
    COUNTROWS(EXCEPT(_Dogs, _Rabies))
    Distemper Missing = 
VAR _Distemper = 
    CALCULATETABLE(
        VALUES('Table'[Dog_Name]),
        'Table'[Requirements] = "Distemper"
    )
VAR _Dogs = 
    VALUES('Table'[Dog_Name])
RETURN 
    COUNTROWS(EXCEPT(_Dogs, _Distemper))

     

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Kedar_Pande's avatar
    Kedar_Pande
    Icon for Super User rankSuper User
    1 year ago

    RichOB 

    Create a Measure to Count Missing Vaccinations:

    MissingVaccinations = 
    VAR VaccinationList = {"Parvo", "Rabies", "Distemper"}
    VAR AllDogs = DISTINCT('Table'[Dog_Name])
    VAR MissingCounts = 
    SUMX(
    GENERATE(
    AllDogs,
    ADDCOLUMNS(
    {UNION(VaccinationList)},
    "Missing",
    NOT(
    CONTAINS('Table', 'Table'[Dog_Name], EARLIER([Dog_Name]), 'Table'[Requirements], EARLIER([Value]))
    )
    )
    ),
    IF([Missing], 1, 0)
    )
    RETURN
    MissingCounts

    💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
    Cheers,
    Kedar
    Connect on LinkedIn

  • olgad's avatar
    olgad
    Icon for Resident Rockstar rankResident Rockstar
    1 year ago

    Hi RichOB ,

    if the number of vaccinations stays constant, then

    you can use such: 

    If (Calculate (Count(Requirements), (allexcept('Table', Dog_Name) <3, "red") 

     

     

     

  • RichOB's avatar
    RichOB
    Icon for Post Partisan rankPost Partisan
    1 year ago

    Hi Kedar_Pande , what value exactly is the part Earlier ([VALUE]))) in the Contains row relating to? It seems to be causing an error for me.

     

    Thanks

    Rich

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi RichOB ,

    Based on the description, create the new measure for each vaccination to calculate the count missing.

     

    Parvo_Missing = 
VAR _Parvo = 
    CALCULATETABLE(
        VALUES('Table'[Dog_Name]),
        'Table'[Requirements] = "Parvo"
    )
VAR _Dogs = 
    VALUES('Table'[Dog_Name])
RETURN 
    COUNTROWS(EXCEPT(_Dogs, _Parvo))

     

     

    Rabies Missing = 
VAR _Rabies = 
    CALCULATETABLE(
        VALUES('Table'[Dog_Name]),
        'Table'[Requirements] = "Rabies"
    )
VAR _Dogs = 
    VALUES('Table'[Dog_Name])
RETURN 
    COUNTROWS(EXCEPT(_Dogs, _Rabies))
    Distemper Missing = 
VAR _Distemper = 
    CALCULATETABLE(
        VALUES('Table'[Dog_Name]),
        'Table'[Requirements] = "Distemper"
    )
VAR _Dogs = 
    VALUES('Table'[Dog_Name])
RETURN 
    COUNTROWS(EXCEPT(_Dogs, _Distemper))

     

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.