Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper II
Helper II

DAX for USERRELATIONSHIP with 2 DIM tables and waterfall structure


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

Super User
Super User

@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

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

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors