DAX for USERRELATIONSHIP with 2 DIM tables and waterfall structure

[olegkazanskyi]

Hello,

I have this schema with 2 inactive relationships and 1 active.

This is a small part of a more complex model as an example for simplicity of explanation of the issue I am trying to solve.

The PBIX is also attached.

Link with the PBIX 

The Activity table can have blanks for the workspace ID or for the Report ID, depending on the activities.

I want to get a matrix with workspaces and reports combined, but I can't find a way to make it work.
No Report values show up under the workspace hierarchy.

Here is my formula.

Count_Activities =

SWITCH (

    TRUE (),

     ISFILTERED ( report ) && ISFILTERED ( workspaces ),

        CALCULATE (

            COUNTROWS ( Activities ),

            USERELATIONSHIP ( report[id], Activities[Report ID] )

        ),

    ISFILTERED ( workspaces ),

        CALCULATE (

            COUNTROWS ( Activities ),

            USERELATIONSHIP ( workspaces[id], Activities[Workspace ID] )

        )

)

I can make it work for reports and workspaces on different visuals, but not when they are together.

[amitchandak]

@olegkazanskyi , you should merge report and workspace tables into one dimension and use that to join and filter Activities

You can use power query merge

 https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[olegkazanskyi]

@amitchandak , 
I may need a little more explanation if I may ask.
When I merge the two tables, as a result, I get many-to-many relations between  workspace_ID as there are duplicates in the new table now.
I can't make a relation solely by the report_id because of the Activity table structure.  A single activity can be related to the report or relevant only to the workspace.