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

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.

6 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    1 year ago

    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], ", ")

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    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.

  • 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.