Power BI Table report removing rows when columns from related tables have no corresponding value

[Bls-80]

I'm having issues creating a table report in power bi.  Scenario: We have a table of inspections in one report.  From this zero to multiple actions can be added to another (there is a corresponding ID to create a relationship), they can also add zero to multiple requests to another table, and from these requests can also create zero to multiple actions.  I have a model with a relationship between the three tables.  I'm trying to create a report which shows certain details from all three tables so corresponding actions and requests can be tracked more easily by teams. As soon as I add values from the actions and/or requests tables into the visual, any inspection that doesn't have a corresponding action and/or request disappears.  I need these to be visible in the report (with blanks in the corresponding columns) as well.  Is this possible?

[Bls-80]

hi @Anonymous, sorry for the late response.  See below. If there was more data in my made-up dataset there would be a 1 to many relationship between inspections and  Requests (from each inspection you can create many requests). Re my output - human error - there should only be one output for Inspection 3! Sorry.  Appreciate any advice/insight you could provide. Thanks

[Anonymous · Accepted solution]

Hi @Bls-80,

Thank you for your additional information.

Please use following dax code to create a calcualted table as shown below:

CombinedTable2 = 
VAR AllInspectionIDs =
   SELECTCOLUMNS(
       'Inspection',
       "Inspection", 'Inspection'[InspectionID]
   )
VAR InspectionInRequest =
   SELECTCOLUMNS(
       'Requests',
       "Inspection", 'Requests'[RelatedID]
   )
VAR InspectionInAction =
   SELECTCOLUMNS(
       'Actions',
       "Inspection", 'Actions'[RelatedID]
   )
VAR InspectionNotInRequestOrAction =
   EXCEPT(
       AllInspectionIDs,
       UNION(
           InspectionInRequest,
           InspectionInAction
       )
   )
VAR InspectionsWithBlanks =
   ADDCOLUMNS(
       InspectionNotInRequestOrAction,
       "Request", BLANK(),
       "Action", BLANK()
   )
RETURN
FILTER(
   UNION(
       InspectionsWithBlanks,
       SELECTCOLUMNS(
           'Requests',
           "Inspection", IF(ISBLANK(LOOKUPVALUE(Actions[RelatedID], Actions[RelatedID], Requests[RequestID])),[RelatedID],BLANK()),
           "Request", IF(ISBLANK(LOOKUPVALUE(Actions[RelatedID], Actions[RelatedID], Requests[RequestID])),[RequestID],BLANK()),
           "Action", BLANK()
       ),
       SELECTCOLUMNS(
           'Actions',
           "Inspection", IF(ISBLANK(LOOKUPVALUE('Actions'[RelatedID], 'Actions'[RelatedID], RELATED(Requests[RequestID]))),'Actions'[RelatedID],LOOKUPVALUE(Requests[RelatedID],Requests[RequestID],'Actions'[RelatedID])),
           "Request", LOOKUPVALUE(Requests[RequestID],Requests[RequestID],'Actions'[RelatedID]),
           "Action", [ActionID]
       )
   ),NOT(ISBLANK([Inspection]))
)

Result for your reference:

Best regards,

Joyce

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

[Bls-80]

Thank you @Anonymous . I'll try that this weekend and accept. I had thought the relationship would have been enough so was on completely the wrong path. It will take me a while to break this down and understand but I really appreciate your help. 

[Anonymous]

Hi @Bls-80 ,

Since the relatedid of the Action table is in both inspection and request, what is the relationship between the three tables?

Also, in your output, why does inspection id 3 have three duplicate rows?

Please feel free to reply for better understanding and testing.

Best regards,

Joyce

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