October 28 & 29: Experts share their secrets on how to pass the Fabric Analytics Engineer certification exam—live. Learn more
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
Solved! Go to Solution.
Hi @MeenaSankar ,
Try the following measure:
Total Users = var locationvalues =VALUES(Sales[location])
Return
CALCULATE(COUNTROWS(Users), Users[location_id] in locationvalues)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MeenaSankar ,
Try the following measure:
Total Users = var locationvalues =VALUES(Sales[location])
Return
CALCULATE(COUNTROWS(Users), Users[location_id] in locationvalues)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank 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
User | Count |
---|---|
27 | |
25 | |
18 | |
16 | |
14 |
User | Count |
---|---|
46 | |
18 | |
12 | |
11 | |
9 |