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

[Jadegirlify]

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 contain a list of all the Breathing Test Operators in the organization with columns Operator ID, Operator Name, and SP ID.  Some of these operators are not performing brething test as they should so they are not appearing in the Breathing_Test table, which other would appear in the Breathing_Test table in one year and not appear at all in another year.

I need to create a table visual listing the names and IDs of operators that did not perform any breathing test/appear in the Breathing_Test Table(per each year) .

Basically want to know how many Breathing Test Operators are not performing breathing tests in each year, based on the records from the Breathing_Test Table. 

[v-yohua-msft · Accepted solution]

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]

@v-yohua-msft 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 

[v-yohua-msft]

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]

Thank you so much! Exactly what I needed 

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[FreemanZ]

hi @Jadegirlify ,

Supposing you have a one-many relationship between Breathing_Test and Master_Operators, and you have a calendar table joined with Breathing_Test.

Try to plot a visual with calendar[year] and such measures:

UnperfomingCount=

VAR _perfomlist = VALUES(Test[Name])

VAR _alllist = ALL(operators[Name])

VAR _unperfomlist = EXCEPT(_alllist, _perfomlist)

RETURN COUNTROWS(_unperfomlist)

UnperfomingList =

VAR _perfomlist = VALUES(Test[Name])

VAR _alllist = ALL(operators[Name])

VAR _unperfomlist = EXCEPT(_alllist, _perfomlist)

RETURN 

CONCATENATEX(_unperfomlist, operators[Name], ", ")