Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am working on a budget tool to track budget vs. Actuals. I have 2 fact tables, one for the budget and one for actuals. The grain is location, dept, acct and date. Everything is working fine so far. I have two questions about modeling 2 situations.
1. There is a report that shows budget vs. Actuals for each dept owner.
2. Implement RLS for people based on association with Dept.
I have created a bridge table for Dept Owner which has location,dept and owner keys. there can be multiple owners based on the location.
I created relationship between Facts and Dims, bridge table and dims. On my report, my grand total is not appearing correct. It shows sum for entire year. I have slicers for year and month. my report has Owner, Dept, Account and when I drill down, the numbers appear to be correct. Only when I collapse the hierarchy or at the Owner level, the numbers are not getting filtered. It works sometime in by test model, but not on real data where I am using AAS.
I am wondering if I am missing anything. Will the same model work for RLS?
Any help is appreciated.
@RSSapre , This model seems fine the only reason the grand total is coming wrong if your measure usages row context. In such case you have use values or summarize to get the correct total
sumx(values(Date[Min-Year]), [Measure])
or
sumx(summarize(Table, Dept[Dept],Date[Month-year], "_1",[measure]),[_1])
can You share the calculation.? Can you share sample data and sample output in table format?
Thanks Amit. Here is a sample measure - Plan:=CALCULATE(SUM(FactPlan[Amount])) and I havesimilar for Actuals. These are plain and simple. I creates a sample data and PBIX and it appears to be working. I think the direction of the relationship matter. I thought it is something related to Azure Analysis service or the way I am building my tabular model. my PBIX works with sample data. After building sample data and pbix for you I made few changes to my tabular model and report is working as expected, however, I will continue to test it. I need to extend this to implement RLS for users in department or users related to department and by location so that logged in person can see only data that they are suppose to see. Any pointers on that will be helpful.
I am more that happy to share my sample data and pbix file, however, I do not see an option here to attach those. Copying some sample data and output here.
Budget -
BMTH | Dept | Amount | LocId |
1/1/2020 | 10 | 200 | 1 |
2/1/2020 | 10 | 200 | 1 |
3/1/2020 | 10 | 200 | 1 |
4/1/2020 | 10 | 200 | 1 |
5/1/2020 | 10 | 200 | 1 |
6/1/2020 | 10 | 200 | 1 |
7/1/2020 | 10 | 200 | 1 |
8/1/2020 | 10 | 200 | 1 |
9/1/2020 | 10 | 200 | 1 |
10/1/2020 | 10 | 200 | 1 |
11/1/2020 | 10 | 200 | 1 |
12/1/2020 | 10 | 200 | 1 |
1/1/2020 | 20 | 200 | 1 |
2/1/2020 | 20 | 200 | 1 |
3/1/2020 | 20 | 200 | 1 |
4/1/2020 | 20 | 200 | 1 |
5/1/2020 | 20 | 200 | 1 |
6/1/2020 | 20 | 200 | 1 |
7/1/2020 | 20 | 200 | 1 |
8/1/2020 | 20 | 200 | 1 |
9/1/2020 | 20 | 200 | 1 |
10/1/2020 | 20 | 200 | 1 |
11/1/2020 | 20 | 200 | 1 |
12/1/2020 | 20 | 200 | 1 |
2/1/2020 | 30 | 100 | 2 |
3/1/2020 | 30 | 100 | 2 |
4/1/2020 | 30 | 100 | 2 |
5/1/2020 | 30 | 100 | 2 |
6/1/2020 | 30 | 100 | 2 |
7/1/2020 | 30 | 100 | 2 |
8/1/2020 | 30 | 100 | 2 |
9/1/2020 | 30 | 100 | 2 |
10/1/2020 | 30 | 100 | 2 |
11/1/2020 | 30 | 100 | 2 |
12/1/2020 | 30 | 100 | 2 |
1/1/2020 | 40 | 100 | 2 |
2/1/2020 | 40 | 100 | 2 |
3/1/2020 | 40 | 100 | 2 |
4/1/2020 | 40 | 100 | 2 |
5/1/2020 | 40 | 100 | 2 |
6/1/2020 | 40 | 100 | 2 |
7/1/2020 | 40 | 100 | 2 |
8/1/2020 | 40 | 100 | 2 |
9/1/2020 | 40 | 100 | 2 |
10/1/2020 | 40 | 100 | 2 |
11/1/2020 | 40 | 100 | 2 |
12/1/2020 | 40 | 100 | 2 |
Actuals -
AMTH | Dept | Amount | LocId |
1/1/2020 | 10 | 100 | 1 |
2/1/2020 | 10 | 100 | 1 |
3/1/2020 | 10 | 100 | 1 |
4/1/2020 | 10 | 100 | 1 |
5/1/2020 | 10 | 100 | 1 |
6/1/2020 | 10 | 100 | 1 |
7/1/2020 | 10 | 100 | 1 |
1/1/2020 | 20 | 100 | 1 |
2/1/2020 | 20 | 100 | 1 |
3/1/2020 | 20 | 100 | 1 |
4/1/2020 | 20 | 100 | 1 |
5/1/2020 | 20 | 100 | 1 |
6/1/2020 | 20 | 100 | 1 |
7/1/2020 | 20 | 100 | 1 |
1/1/2020 | 30 | 100 | 1 |
2/1/2020 | 30 | 100 | 1 |
3/1/2020 | 30 | 100 | 1 |
4/1/2020 | 30 | 100 | 1 |
5/1/2020 | 30 | 100 | 1 |
6/1/2020 | 30 | 100 | 1 |
7/1/2020 | 30 | 100 | 1 |
1/1/2020 | 40 | 100 | 1 |
2/1/2020 | 40 | 100 | 1 |
3/1/2020 | 40 | 100 | 1 |
4/1/2020 | 40 | 100 | 1 |
5/1/2020 | 40 | 100 | 1 |
6/1/2020 | 40 | 100 | 1 |
7/1/2020 | 40 | 100 | 1 |
1/1/2020 | 10 | 20 | 2 |
2/1/2020 | 10 | 20 | 2 |
3/1/2020 | 10 | 20 | 2 |
4/1/2020 | 10 | 20 | 2 |
5/1/2020 | 10 | 20 | 2 |
6/1/2020 | 10 | 20 | 2 |
7/1/2020 | 10 | 20 | 2 |
1/1/2020 | 20 | 20 | 2 |
2/1/2020 | 20 | 20 | 2 |
3/1/2020 | 20 | 20 | 2 |
4/1/2020 | 20 | 20 | 2 |
5/1/2020 | 20 | 20 | 2 |
6/1/2020 | 20 | 20 | 2 |
7/1/2020 | 20 | 20 | 2 |
1/1/2020 | 30 | 20 | 2 |
2/1/2020 | 30 | 20 | 2 |
3/1/2020 | 30 | 20 | 2 |
4/1/2020 | 30 | 20 | 2 |
5/1/2020 | 30 | 20 | 2 |
6/1/2020 | 30 | 20 | 2 |
7/1/2020 | 30 | 20 | 2 |
1/1/2020 | 40 | 20 | 2 |
2/1/2020 | 40 | 20 | 2 |
3/1/2020 | 40 | 20 | 2 |
4/1/2020 | 40 | 20 | 2 |
5/1/2020 | 40 | 20 | 2 |
6/1/2020 | 40 | 20 | 2 |
7/1/2020 | 40 | 20 | 2 |
Dept -
DeptID | DeptName |
10 | Dept 1 |
20 | Dept 2 |
30 | Dept 3 |
40 | Dept 4 |
location -
LocId | Location |
1 | WA |
2 | NY |
3 | TX |
Dept Owner -
DeptID | LocId | Owner |
10 | 1 | A |
20 | 1 | A |
30 | 1 | B |
40 | 1 | C |
10 | 2 | A |
20 | 2 | D |
30 | 2 | E |
40 | 2 | F |
10 | 3 | F |
20 | 3 | C |
30 | 3 | G |
40 | 3 | G |
For DimDate, I used calendarauto() and added custom columns. Here is my output -
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |