Hi all,
I'm trying to change existing reports in accordance to recent demands for OLS (Object Level Security). What I'm struggling with is how to access a field parameter in a dynamic way in external DAX measures (or in Top N filters (in the value well)).
Since OLS effectively deletes / hides fields from the model, the initial result is many tables ending up with errors to hide the content. This is the result of fields that are no longer available, or filters that are applied, but don't exist anymore. Since this is not the desired end-result, I've been adding some functionality to provide a more user friendly experience. So far so good, everything works like intended (tables / cards / titles / etc. are all adjusted dynamically based on the role selected). Except...
When I'm using a chart (e.g. a Clustered bar chart or a funnel) that leverages a Top N filter, I am not able to reference the Field parameters dynamically to get this working resulting in a chart showing me all values instead.
I have a couple of measures, which are secured depending on the users security level (below is a simplified example, they are not the actual measures, but the concept is similar):
amount = sum(sales[amount]) -- available for everyone (security level 2 and below)
cost = sumx(sales, sales[costprice] * sales[amount]) -- available for SL 0
turnover = sumx(sales, sales[salesprice] * sales[amount]) -- available for SL 1 / 0
margin = [turnover] - [cost] -- (because of the cost measure, only available for 0)
I have a Field parameter like this. I'm using RLS to filter the orderfield for the appropriate security level and decide which measure needs to be used. Normally that would imply there is always at maximum one measure available at all times.
OLS Top N measure = {
("margin", NAMEOF('measureTable'[margin]), 0),
("turnover", NAMEOF('measureTable'[turnover]), 1),
("amount", NAMEOF('measureTable'[amount]), 2)
}
Basically, what I'm trying to do is create a measure like below, to determine dynamically which values needs to be used for the Top N determination. However, this will not work due to OLS. It creates a dependancy on the actual measures below and as such will delete this measure when OLS is applied with insufficient clearance.
measure_To_Use_In_Top_N_Filter_Value_Field_Well =
Switch
(
selectedvalue('OLS Top N measure'[OLS Top N measure Order]),
0, [margin],
1, [turnover],
2, [amount],
)
So instead, what I'm trying to do, is access the Field Parameter instead to fall back on the next best measure. However, I've spend quite some time already and I'm no closer to finding the solution. In essence, whenever I try to access the Fields value, it obviously tells me this is a String, which it cannot do much with:
Top N measure rank value =
RANKX
(
all('salesheaders'[customerNo]),
SUM('OLS Top N measure'[OLS Top N measure Fields])
)
So what puzzles me and gives me hope at the same time: Whenever I use the field parameter in a field well of a visual, that visual is able to translate this to a measure (Kind of like the indirect functionality in Excel), yet when I try to do the same in DAX, I'm unsuccessful. I've also been wondering about DMV queries, however, I have little to no experience with those, so maybe I'm overcomplication things, or maybe it is simply not possible.
What am I missing here?
Thank you in advance!
