Forum Discussion

Jadegirlify's avatar
Jadegirlify
Helper I
1 year ago
Solved

I need help creating a Table Visual showing IDs Missing From Another Table

In PowerBI, I have a table called Breathing_Test which contains column called BreathingTest ID, Operator Name, Operator ID, Breathing Test Date. I have another table called Master_Operators which co...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi, Jadegirlify 

    Based on your information, I create sample tables:

    Create a Calendar table, create a column named Year and manage relationship:

    Calendar = CALENDAR(MIN(Breathing_Test[Breathing Test Date]), MAX(Breathing_Test[Breathing Test Date]))

Year = YEAR(Calendar[Date])

     

    Then create a new measure:

    OperatorsNotPerformingTests1 = 
CALCULATE(
    MAX(Master_Operators[Operator ID]),
    FILTER(
        Master_Operators,
        NOT(
            Master_Operators[Operator ID] IN 
            SELECTCOLUMNS(
                FILTER(Breathing_Test, YEAR(Breathing_Test[Breathing Test Date]) = SELECTEDVALUE(Calendar[Year])),
                "Operator ID", Breathing_Test[Operator ID]
            )
        )
    )
)

     

    Use Master_Operators[Operator Name] and Master_Operators[Operator ID] as columns. Add a measure to the filter of the table visualization. Add a slicer for Calendar[Year] to filter the data by year. Here is my preview:

     

     

    How to Get Your Question Answered Quickly

    Best Regards

    Yongkang Hua

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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi, Jadegirlify 

Based on your information, I create sample tables:

Create a Calendar table, create a column named Year and manage relationship:

Calendar = CALENDAR(MIN(Breathing_Test[Breathing Test Date]), MAX(Breathing_Test[Breathing Test Date]))

Year = YEAR(Calendar[Date])

 

Then create a new measure:

OperatorsNotPerformingTests1 = 
CALCULATE(
    MAX(Master_Operators[Operator ID]),
    FILTER(
        Master_Operators,
        NOT(
            Master_Operators[Operator ID] IN 
            SELECTCOLUMNS(
                FILTER(Breathing_Test, YEAR(Breathing_Test[Breathing Test Date]) = SELECTEDVALUE(Calendar[Year])),
                "Operator ID", Breathing_Test[Operator ID]
            )
        )
    )
)

 

Use Master_Operators[Operator Name] and Master_Operators[Operator ID] as columns. Add a measure to the filter of the table visualization. Add a slicer for Calendar[Year] to filter the data by year. Here is my preview:

 

 

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

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

  • Jadegirlify's avatar
    Jadegirlify
    Helper I
    1 year ago

    Anonymous Thanks so much! Is there anyway to show the count as well, using a visual card. It won't show the count since it's a measure 

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi, Jadegirlify 

      All you need to do is modify the metric and use the Countrows function:

      OperatorsNotPerformingTests1 = 
CALCULATE(
    COUNTROWS(Master_Operators),
    FILTER(
        Master_Operators,
        NOT(
            Master_Operators[Operator ID] IN 
            SELECTCOLUMNS(
                FILTER(Breathing_Test, YEAR(Breathing_Test[Breathing Test Date]) = SELECTEDVALUE(Calendar[Year])),
                "Operator ID", Breathing_Test[Operator ID]
            )
        )
    )
)

       

      Then add a card visual and put measure in it, here is my preview:

       

      How to Get Your Question Answered Quickly

      Best Regards

      Yongkang Hua

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

      • Jadegirlify's avatar
        Jadegirlify
        Helper I
        1 year ago

        Thank you so much! Exactly what I needed