Reporting on Degenerate dimension from 2 fact tables with a dimension in between

[MeenaSankar]

Hello,

I have 2 fact tables (user, sales) and a dimension table (loaction) in between. Now I need to report on a degenrate dimension on one of the fact table (called brand in sales), a measure on the same fact table (count rows in sales) and a measure on the second fact table (count of related users).

Desired table is what I am getting when I join the dimesnions and metrics together. However as you cab see the user count is the same for all brands. But I really would like to see is what  are the count of users for those locations from where the brands were purchased. So I would like something like this.

As you can see adidas was bought frpom location 1 which has only 2 users. 

I cant create a many to many relation between users and sales as I have a RLS set up using profile and staff.

Is there any way I can solve this.

Please please please help.

@johnt75 , @MFelix , @OwenAuger 

Regards,

Meena

[MFelix · Accepted solution]

Hi @MeenaSankar ,

Try the following measure:

Total Users = var locationvalues =VALUES(Sales[location])
Return
CALCULATE(COUNTROWS(Users), Users[location_id] in locationvalues)

[MeenaSankar]

Thank you so much @MFelix 

That seems to be working!

As a last resort, I was even planning to make the relation between Sales and location bidirectional😂 But now that wont be necessary