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.
Hi,
I have a table with multiple rows for multiple stores for given days. I am looking to acheive the maximum for the given day between stores. This seems to work with the Calculated Column (which I prefer to measure):
Max Percent = CALCULATE(MAX('Table[Percent]), FILTER('Table', 'Table'[Dates]=earlier('Table'[Dates])))
However once I deploy Roles to filter out only the selected stores, the results are still pulling the maximum from all the stores and not just the filtered stores:
StoreID | Date | Day | Percent | Max Percent |
12173 | Friday, 13 July 2018 | Fri | 1.64 | 35.71 |
3097 | Friday, 13 July 2018 | Fri | 7.51 | 35.71 |
4763 | Friday, 13 July 2018 | Fri | 8.09 | 35.71 |
5183 | Friday, 13 July 2018 | Fri | 8.06 | 35.71 |
12243 | Friday, 13 July 2018 | Fri | 15.38 | 35.71 |
5492 | Friday, 13 July 2018 | Fri | 18.05 | 35.71 |
12161 | Friday, 13 July 2018 | Fri | 9.48 | 35.71 |
13248 | Friday, 13 July 2018 | Fri | 4.65 | 35.71 |
10945 | Friday, 13 July 2018 | Fri | 14.29 | 35.71 |
8684 | Friday, 13 July 2018 | Fri | 9.18 | 35.71 |
1846 | Friday, 13 July 2018 | Fri | 3.68 | 35.71 |
3468 | Friday, 13 July 2018 | Fri | 6.48 | 35.71 |
6952 | Friday, 13 July 2018 | Fri | 23.13 | 35.71 |
1631 | Friday, 13 July 2018 | Fri | 15.65 | 35.71 |
13461 | Friday, 13 July 2018 | Fri | 2.34 | 35.71 |
5455 | Friday, 13 July 2018 | Fri | 6.63 | 35.71 |
538 | Friday, 13 July 2018 | Fri | 5.94 | 35.71 |
2017 | Friday, 13 July 2018 | Fri | 6.58 | 35.71 |
9012 | Friday, 13 July 2018 | Fri | 3.57 | 35.71 |
8779 | Friday, 13 July 2018 | Fri | 18.02 | 35.71 |
10000 | Friday, 13 July 2018 | Fri | 16.36 | 35.71 |
5788 | Friday, 13 July 2018 | Fri | 3.33 | 35.71 |
9198 | Friday, 13 July 2018 | Fri | 13.6 | 35.71 |
5049 | Friday, 13 July 2018 | Fri | 3.36 | 35.71 |
12825 | Friday, 13 July 2018 | Fri | 5.6 | 35.71 |
1610 | Friday, 13 July 2018 | Fri | 9.14 | 35.71 |
13797 | Friday, 13 July 2018 | Fri | 4.66 | 35.71 |
1175 | Friday, 13 July 2018 | Fri | 4.73 | 35.71 |
1458 | Friday, 13 July 2018 | Fri | 8.18 | 35.71 |
4641 | Friday, 13 July 2018 | Fri | 10.84 | 35.71 |
14019 | Friday, 13 July 2018 | Fri | 5.84 | 35.71 |
6070 | Friday, 13 July 2018 | Fri | 4.62 | 35.71 |
6995 | Friday, 13 July 2018 | Fri | 1.42 | 35.71 |
10683 | Friday, 13 July 2018 | Fri | 6.84 | 35.71 |
9165 | Friday, 13 July 2018 | Fri | 14.49 | 35.71 |
12739 | Friday, 13 July 2018 | Fri | 20.76 | 35.71 |
2692 | Friday, 13 July 2018 | Fri | 19.95 | 35.71 |
11088 | Friday, 13 July 2018 | Fri | 8.33 | 35.71 |
4770 | Friday, 13 July 2018 | Fri | 9.57 | 35.71 |
10886 | Friday, 13 July 2018 | Fri | 26.56 | 35.71 |
8118 | Friday, 13 July 2018 | Fri | 18.97 | 35.71 |
10586 | Friday, 13 July 2018 | Fri | 35.71 | 35.71 |
1900 | Friday, 13 July 2018 | Fri | 6.44 | 35.71 |
6680 | Friday, 13 July 2018 | Fri | 8.06 | 35.71 |
12443 | Friday, 13 July 2018 | Fri | 2.17 | 35.71 |
10198 | Friday, 13 July 2018 | Fri | 6.51 | 35.71 |
2742 | Friday, 13 July 2018 | Fri | 8.33 | 35.71 |
1321 | Friday, 13 July 2018 | Fri | 8.4 | 35.71 |
6177 | Friday, 13 July 2018 | Fri | 28.98 | 35.71 |
12672 | Friday, 13 July 2018 | Fri | 2.66 | 35.71 |
10510 | Friday, 13 July 2018 | Fri | 13.55 | 35.71 |
9134 | Friday, 13 July 2018 | Fri | 10.71 | 35.71 |
343 | Friday, 13 July 2018 | Fri | 1.12 | 35.71 |
1335 | Friday, 13 July 2018 | Fri | 1.63 | 35.71 |
1478 | Friday, 13 July 2018 | Fri | 2.21 | 35.71 |
12173 | Thursday, 12 July 2018 | Thu | 0.65 | 62.5 |
3097 | Thursday, 12 July 2018 | Thu | 3.28 | 62.5 |
4763 | Thursday, 12 July 2018 | Thu | 4.03 | 62.5 |
5183 | Thursday, 12 July 2018 | Thu | 8.6 | 62.5 |
12243 | Thursday, 12 July 2018 | Thu | 12.55 | 62.5 |
5492 | Thursday, 12 July 2018 | Thu | 20.39 | 62.5 |
12161 | Thursday, 12 July 2018 | Thu | 8.55 | 62.5 |
13248 | Thursday, 12 July 2018 | Thu | 3.57 | 62.5 |
10945 | Thursday, 12 July 2018 | Thu | 25 | 62.5 |
8684 | Thursday, 12 July 2018 | Thu | 9.16 | 62.5 |
1846 | Thursday, 12 July 2018 | Thu | 7.91 | 62.5 |
3468 | Thursday, 12 July 2018 | Thu | 3.2 | 62.5 |
6952 | Thursday, 12 July 2018 | Thu | 22.64 | 62.5 |
1631 | Thursday, 12 July 2018 | Thu | 10.61 | 62.5 |
13461 | Thursday, 12 July 2018 | Thu | 5.41 | 62.5 |
5455 | Thursday, 12 July 2018 | Thu | 8.02 | 62.5 |
538 | Thursday, 12 July 2018 | Thu | 6.38 | 62.5 |
2017 | Thursday, 12 July 2018 | Thu | 5.81 | 62.5 |
9012 | Thursday, 12 July 2018 | Thu | 7.59 | 62.5 |
8779 | Thursday, 12 July 2018 | Thu | 11.76 | 62.5 |
10000 | Thursday, 12 July 2018 | Thu | 7.46 | 62.5 |
5788 | Thursday, 12 July 2018 | Thu | 4.46 | 62.5 |
9198 | Thursday, 12 July 2018 | Thu | 28.04 | 62.5 |
5049 | Thursday, 12 July 2018 | Thu | 2.99 | 62.5 |
12825 | Thursday, 12 July 2018 | Thu | 8.19 | 62.5 |
1610 | Thursday, 12 July 2018 | Thu | 4.63 | 62.5 |
13797 | Thursday, 12 July 2018 | Thu | 8.92 | 62.5 |
1175 | Thursday, 12 July 2018 | Thu | 4.55 | 62.5 |
1458 | Thursday, 12 July 2018 | Thu | 6.67 | 62.5 |
4641 | Thursday, 12 July 2018 | Thu | 5.86 | 62.5 |
14019 | Thursday, 12 July 2018 | Thu | 5.15 | 62.5 |
6070 | Thursday, 12 July 2018 | Thu | 9.09 | 62.5 |
6995 | Thursday, 12 July 2018 | Thu | 5.81 | 62.5 |
10683 | Thursday, 12 July 2018 | Thu | 12.39 | 62.5 |
9165 | Thursday, 12 July 2018 | Thu | 14.89 | 62.5 |
12739 | Thursday, 12 July 2018 | Thu | 21.7 | 62.5 |
2692 | Thursday, 12 July 2018 | Thu | 21.09 | 62.5 |
11088 | Thursday, 12 July 2018 | Thu | 16.5 | 62.5 |
4770 | Thursday, 12 July 2018 | Thu | 8.86 | 62.5 |
10886 | Thursday, 12 July 2018 | Thu | 26.09 | 62.5 |
8118 | Thursday, 12 July 2018 | Thu | 28.57 | 62.5 |
10586 | Thursday, 12 July 2018 | Thu | 36.11 | 62.5 |
1900 | Thursday, 12 July 2018 | Thu | 4.11 | 62.5 |
6680 | Thursday, 12 July 2018 | Thu | 62.5 | 62.5 |
12443 | Thursday, 12 July 2018 | Thu | 1.08 | 62.5 |
10198 | Thursday, 12 July 2018 | Thu | 7.03 | 62.5 |
2742 | Thursday, 12 July 2018 | Thu | 7.55 | 62.5 |
1321 | Thursday, 12 July 2018 | Thu | 7.09 | 62.5 |
6177 | Thursday, 12 July 2018 | Thu | 26.21 | 62.5 |
12672 | Thursday, 12 July 2018 | Thu | 0.77 | 62.5 |
10510 | Thursday, 12 July 2018 | Thu | 0.56 | 62.5 |
9134 | Thursday, 12 July 2018 | Thu | 4.41 | 62.5 |
343 | Thursday, 12 July 2018 | Thu | 4.82 | 62.5 |
1335 | Thursday, 12 July 2018 | Thu | 1.85 | 62.5 |
1478 | Thursday, 12 July 2018 | Thu | 3.27 | 62.5 |
The stores that are being filtered by roles are as per the below (you can see that 62.5 on the 12th which is not in the filtered rows is still seen as maximum).
StoreID | Date | Day | Percent | Max Percent |
10886 | Friday, 13 July 2018 | Fri | 26.56 | 35.71 |
8118 | Friday, 13 July 2018 | Fri | 18.97 | 35.71 |
10586 | Friday, 13 July 2018 | Fri | 35.71 | 35.71 |
10886 | Thursday, 12 July 2018 | Thu | 26.09 | 62.5 |
8118 | Thursday, 12 July 2018 | Thu | 28.57 | 62.5 |
10586 | Thursday, 12 July 2018 | Thu | 36.11 | 62.5 |
Can anyone assit? Is there some filter I am missing in the formula?
Solved! Go to Solution.
Hey,
now that I'm reading your initial post once again ...
This will not work, this is due to the following.
Calculated columns are evaluated just once, and this is on data refresh!
So RLS does filter the stores correctly, but these rows are already containing the value from the calculated column.
Regards,
Tom
@duggy Please create a New Measure as below
Test253 = CALCULATE(MAX(Test253DynamicMax[Percent]),ALLSELECTED(Test253DynamicMax[StoreID]))
Proud to be a PBI Community Champion
Hi,
Can this be acheived as a Calculated Column instead of a measure? The reason being is that once done with a calculated column, there are a couple of other formulas that follows (working out the shortfall of this store compared to maximum, working out how many transactions that amounts to and the turnover that that results in etc). When doing this as a measure and then drilling up to monthy and yearly the totals dont add up but rather establish on the summed line and dont equate as calculated column does.
Hey,
just to be certain ...
I assume you tested the role and are aware of these "limitations", that are described here: https://docs.microsoft.com/en-us/power-bi/service-admin-rls
Can you also provide the definition of the DAX statement(s) that you are using to define the role!
Regards,
Tom
Hey,
now that I'm reading your initial post once again ...
This will not work, this is due to the following.
Calculated columns are evaluated just once, and this is on data refresh!
So RLS does filter the stores correctly, but these rows are already containing the value from the calculated column.
Regards,
Tom
Ok, so if we are using calculated columns (for historical data) but then we remove a store with a filter (not just through the roles) that store data would still be taken into account in the calculated column? If this is the case then it makes sense as to the issue that we are having and cannot resolve with Calculated Columns as it would run the formula on all the stores BEFORE the roles or other filters (and we would have a maximum from an existing store even if it was filtered out in the roles). That would make sense.
So a measure is the only way to proceed.
That being said, how do I overcome the fact that when we are at the lowest drill point (by day) all the data looks good, all the rows calculate correctly, but when we drill up (by month) it doesnt add up all the rows results, instead it "re-runs" the measure on the totals.
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |