Forum Discussion

JinjiNoDie's avatar
JinjiNoDie
New Member
1 year ago
Solved

List missing information in new table

I have two tables with a M:M relationship. Whats the best way to determine if something is missing from the second table when comparing it to the first table? 

 

 

Need Help
  • rohit1991's avatar
    rohit1991
    1 year ago

    To determine if something is missing in the second table compared to the first table in Power BI:

    1. Create Relationships: Establish a relationship between the two tables based on the common fields (e.g., Report Number and Item Number).

    2. Create a New Table: Use a DAX formula like the following to identify missing items:
      MissingItems =
      EXCEPT(
      SELECTCOLUMNS('FirstTable', "Report Number", 'FirstTable'[Report Number], "Item Number", 'FirstTable'[Item Number]),
      SELECTCOLUMNS('SecondTable', "Report Number", 'SecondTable'[Report Number], "Item Number", 'SecondTable'[Item Number]))

    3. Visualize the Output: Use a table visualization to display the MissingItems table, showing the Report Numbers and Item Numbers missing from the second table.

    4. This will directly list all missing combinations of report and item numbers.



3 Replies

  • rohit1991's avatar
    rohit1991
    Super User
    1 year ago

    To determine if something is missing in the second table compared to the first table in Power BI:

    1. Create Relationships: Establish a relationship between the two tables based on the common fields (e.g., Report Number and Item Number).

    2. Create a New Table: Use a DAX formula like the following to identify missing items:
      MissingItems =
      EXCEPT(
      SELECTCOLUMNS('FirstTable', "Report Number", 'FirstTable'[Report Number], "Item Number", 'FirstTable'[Item Number]),
      SELECTCOLUMNS('SecondTable', "Report Number", 'SecondTable'[Report Number], "Item Number", 'SecondTable'[Item Number]))

    3. Visualize the Output: Use a table visualization to display the MissingItems table, showing the Report Numbers and Item Numbers missing from the second table.

    4. This will directly list all missing combinations of report and item numbers.



  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hello,rohit1991 ,thanks for your concern about this issue.

    Your answer is excellent!
    And I would like to share some additional solutions below.
    Hi,JinjiNoDie .I am glad to help you.
    If your two tables are guaranteed to look up the same columns, then you can try creating a virtual table that contains all the data from the table by using the except function on both virtual tables
    like this.

    The EXCEPT function is mainly used to compare two tables and return the rows that exist in the first table but not in the second. Note that the two tables must have the same number of columns, and the order and data types of the columns must match.


    Create two virtual tables, _table1 and _table2, containing the Report Number and Item Number columns in the FirstTable and SecondTable, respectively. Then, use the EXCEPT function to return the rows that are in _table1 but not in _table2.
    This gives you the flexibility to perform data lookups on the original tables without affecting the structure and data of the original tables.
    Below is my test data:

    FirstTable

    Report Number

    Item Number

    25-1

    1001

    25-1

    1002

    25-1

    1004

    25-2

    1001

    25-3

    1002

    25-3

    1003

    25-4

    1101

    25-4

    1102


    SecondTable

    Report Number

    Item Number

    25-1

    1001

    25-4

    1102

    25-1

    1004

    25-2

    1001

    25-3

    1002

    25-3

    1003

     


    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
    Best Regards,
    Carson Jian,
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.