Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
duggy
Advocate II
Advocate II

MAXX with Earlier seeing outside Role filter

‎03-12-2019 07:22 AM

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:

 

StoreIDDateDayPercentMax Percent
12173Friday, 13 July 2018Fri1.6435.71
3097Friday, 13 July 2018Fri7.5135.71
4763Friday, 13 July 2018Fri8.0935.71
5183Friday, 13 July 2018Fri8.0635.71
12243Friday, 13 July 2018Fri15.3835.71
5492Friday, 13 July 2018Fri18.0535.71
12161Friday, 13 July 2018Fri9.4835.71
13248Friday, 13 July 2018Fri4.6535.71
10945Friday, 13 July 2018Fri14.2935.71
8684Friday, 13 July 2018Fri9.1835.71
1846Friday, 13 July 2018Fri3.6835.71
3468Friday, 13 July 2018Fri6.4835.71
6952Friday, 13 July 2018Fri23.1335.71
1631Friday, 13 July 2018Fri15.6535.71
13461Friday, 13 July 2018Fri2.3435.71
5455Friday, 13 July 2018Fri6.6335.71
538Friday, 13 July 2018Fri5.9435.71
2017Friday, 13 July 2018Fri6.5835.71
9012Friday, 13 July 2018Fri3.5735.71
8779Friday, 13 July 2018Fri18.0235.71
10000Friday, 13 July 2018Fri16.3635.71
5788Friday, 13 July 2018Fri3.3335.71
9198Friday, 13 July 2018Fri13.635.71
5049Friday, 13 July 2018Fri3.3635.71
12825Friday, 13 July 2018Fri5.635.71
1610Friday, 13 July 2018Fri9.1435.71
13797Friday, 13 July 2018Fri4.6635.71
1175Friday, 13 July 2018Fri4.7335.71
1458Friday, 13 July 2018Fri8.1835.71
4641Friday, 13 July 2018Fri10.8435.71
14019Friday, 13 July 2018Fri5.8435.71
6070Friday, 13 July 2018Fri4.6235.71
6995Friday, 13 July 2018Fri1.4235.71
10683Friday, 13 July 2018Fri6.8435.71
9165Friday, 13 July 2018Fri14.4935.71
12739Friday, 13 July 2018Fri20.7635.71
2692Friday, 13 July 2018Fri19.9535.71
11088Friday, 13 July 2018Fri8.3335.71
4770Friday, 13 July 2018Fri9.5735.71
10886Friday, 13 July 2018Fri26.5635.71
8118Friday, 13 July 2018Fri18.9735.71
10586Friday, 13 July 2018Fri35.7135.71
1900Friday, 13 July 2018Fri6.4435.71
6680Friday, 13 July 2018Fri8.0635.71
12443Friday, 13 July 2018Fri2.1735.71
10198Friday, 13 July 2018Fri6.5135.71
2742Friday, 13 July 2018Fri8.3335.71
1321Friday, 13 July 2018Fri8.435.71
6177Friday, 13 July 2018Fri28.9835.71
12672Friday, 13 July 2018Fri2.6635.71
10510Friday, 13 July 2018Fri13.5535.71
9134Friday, 13 July 2018Fri10.7135.71
343Friday, 13 July 2018Fri1.1235.71
1335Friday, 13 July 2018Fri1.6335.71
1478Friday, 13 July 2018Fri2.2135.71
12173Thursday, 12 July 2018Thu0.6562.5
3097Thursday, 12 July 2018Thu3.2862.5
4763Thursday, 12 July 2018Thu4.0362.5
5183Thursday, 12 July 2018Thu8.662.5
12243Thursday, 12 July 2018Thu12.5562.5
5492Thursday, 12 July 2018Thu20.3962.5
12161Thursday, 12 July 2018Thu8.5562.5
13248Thursday, 12 July 2018Thu3.5762.5
10945Thursday, 12 July 2018Thu2562.5
8684Thursday, 12 July 2018Thu9.1662.5
1846Thursday, 12 July 2018Thu7.9162.5
3468Thursday, 12 July 2018Thu3.262.5
6952Thursday, 12 July 2018Thu22.6462.5
1631Thursday, 12 July 2018Thu10.6162.5
13461Thursday, 12 July 2018Thu5.4162.5
5455Thursday, 12 July 2018Thu8.0262.5
538Thursday, 12 July 2018Thu6.3862.5
2017Thursday, 12 July 2018Thu5.8162.5
9012Thursday, 12 July 2018Thu7.5962.5
8779Thursday, 12 July 2018Thu11.7662.5
10000Thursday, 12 July 2018Thu7.4662.5
5788Thursday, 12 July 2018Thu4.4662.5
9198Thursday, 12 July 2018Thu28.0462.5
5049Thursday, 12 July 2018Thu2.9962.5
12825Thursday, 12 July 2018Thu8.1962.5
1610Thursday, 12 July 2018Thu4.6362.5
13797Thursday, 12 July 2018Thu8.9262.5
1175Thursday, 12 July 2018Thu4.5562.5
1458Thursday, 12 July 2018Thu6.6762.5
4641Thursday, 12 July 2018Thu5.8662.5
14019Thursday, 12 July 2018Thu5.1562.5
6070Thursday, 12 July 2018Thu9.0962.5
6995Thursday, 12 July 2018Thu5.8162.5
10683Thursday, 12 July 2018Thu12.3962.5
9165Thursday, 12 July 2018Thu14.8962.5
12739Thursday, 12 July 2018Thu21.762.5
2692Thursday, 12 July 2018Thu21.0962.5
11088Thursday, 12 July 2018Thu16.562.5
4770Thursday, 12 July 2018Thu8.8662.5
10886Thursday, 12 July 2018Thu26.0962.5
8118Thursday, 12 July 2018Thu28.5762.5
10586Thursday, 12 July 2018Thu36.1162.5
1900Thursday, 12 July 2018Thu4.1162.5
6680Thursday, 12 July 2018Thu62.562.5
12443Thursday, 12 July 2018Thu1.0862.5
10198Thursday, 12 July 2018Thu7.0362.5
2742Thursday, 12 July 2018Thu7.5562.5
1321Thursday, 12 July 2018Thu7.0962.5
6177Thursday, 12 July 2018Thu26.2162.5
12672Thursday, 12 July 2018Thu0.7762.5
10510Thursday, 12 July 2018Thu0.5662.5
9134Thursday, 12 July 2018Thu4.4162.5
343Thursday, 12 July 2018Thu4.8262.5
1335Thursday, 12 July 2018Thu1.8562.5
1478Thursday, 12 July 2018Thu3.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).

 

StoreIDDateDayPercentMax Percent
10886Friday, 13 July 2018Fri26.5635.71
8118Friday, 13 July 2018Fri18.9735.71
10586Friday, 13 July 2018Fri35.7135.71
10886Thursday, 12 July 2018Thu26.0962.5
8118Thursday, 12 July 2018Thu28.5762.5
10586Thursday, 12 July 2018Thu36.11

62.5

 

Can anyone assit? Is there some filter I am missing in the formula?

Labels:
Message 1 of 6
863 Views
0
1 ACCEPTED SOLUTION
TomMartens
Super User
Super User
In response to TomMartens

‎03-12-2019 03:21 PM

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 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

Message 5 of 6
839 Views
0
5 REPLIES 5
PattemManohar
Community Champion
Community Champion

‎03-12-2019 08:41 AM

@duggy  Please create a New Measure as below

 

Test253 = CALCULATE(MAX(Test253DynamicMax[Percent]),ALLSELECTED(Test253DynamicMax[StoreID]))

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Message 2 of 6
854 Views
0
duggy
Advocate II
Advocate II
In response to PattemManohar

‎03-12-2019 02:41 PM

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.

Message 3 of 6
847 Views
0
TomMartens
Super User
Super User
In response to duggy

‎03-12-2019 03:15 PM

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Message 4 of 6
840 Views
0
TomMartens
Super User
Super User
In response to TomMartens

‎03-12-2019 03:21 PM

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 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Message 5 of 6
840 Views
0
duggy
Advocate II
Advocate II
In response to TomMartens

‎03-13-2019 07:40 AM

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.

Message 6 of 6
825 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All