Abstract, from a SQLBI article. Link to the original article on SQLBI.com click here https://sql.bi/817079. I just could recommend to read every single article from SQLBI, Alberto Ferrari XOR Marco Russo in full length. Below some key pattern and notes from my side as my personal abstract.
Pattern to compute ratios, when data is hidden by row-level security.
Pattern 1
Full row-level security
Example Measure
Pct over All =
DIVIDE (
[Sales Amount],
CALCULATE (
[Sales Amount],
ALL ( Customer )
)
)No row level security applied:
| Continent | Sales Amount | Pct over All |
| Australia | 100 | 33.3% |
| Europe | 100 | 33.3% |
| North America | 100 | 33.3% |
| Total | 300 | Total |
Row-level security: „Austrlia, Europe“
| Continent | Sales Amount | Pct over All |
| Australia | 100 | 50.0% |
| Europe | 100 | 50.0% |
| Total | 200 | 100.0% |
If that the goal to achieve, everything is fine.
Pattern 2
For any reason, you need „total sales“ in a row-level managed dataset. In that case it’s possible to materialize „total sales“ in a calculated table.
GrandTotalSales = ROW ( "Grand Total Sales", [Sales Amount] )| Grand Total Sales | |
| 300 |
This table will be not affected by row-level security setting.
To provide more granularity, calculated table could look like this.
SalesNoCustomer =
ADDCOLUMNS (
SUMMARIZE (
Sales,
'Product'[ProductKey],
'Date'[Date],
Store[StoreKey]
),
"Total Sales", [Sales Amount]
)In that case it’s also necessary to create the propper relations.
Measure not affected by row-level security
Pct Not Secured =
DIVIDE (
[Sales Amount],
SUM ( SalesNoCustomer[Total Sales] )
)| Continent | Sales Amount | Pct over All | Pct Not Secured |
| Australia | 100 | 50.0% | 33.3% |
| Europe | 100 | 50.0% | 33.3% |
| Total | 200 | 100.0% | 66.6% |
Pattern 3
Partially exclude DimensionAttributes from row-level security.
Step 1: CalculatedTable, here CustomerAttributes
CustomerAttributes =
VAR T =
SUMMARIZE (
Customer,
Customer[Country],
Customer[State],
Customer[Continent],
Customer[Gender]
)
RETURN
ADDCOLUMNS ( T, "CustomerAttributeKey", ROWNUMBER ( T ) )| Country | State | Continent | Gender | CustomerAttributeKey | |
| xxx | xxx | xxx | xxx | 1 | |
| xxx | xxx | xxx | xxx | 2 | |
| xxx | xxx | xxx | xxx | 3 |
Step 2: Calculated Column, here SalesTable
CustomerAttributeKey =
LOOKUPVALUE (
CustomerAttributes[CustomerAttributeKey],
CustomerAttributes[Country], RELATED ( Customer[Country] ),
CustomerAttributes[State], RELATED ( Customer[State] ),
CustomerAttributes[Continent], RELATED ( Customer[Continent] ),
CustomerAttributes[Gender], RELATED ( Customer[Gender] )
)Step 3: Propagate, CalcCol from Step 2 to SalesNoCustomers
SalesNoCustomer =
ADDCOLUMNS (
SUMMARIZE (
Sales,
Sales[CustomerAttributeKey],
'Product'[ProductKey],
'Date'[Date],
Store[StoreKey]
),
"Total Sales", [Sales Amount]
)Step 4: In that case it’s also necessary to create the propper relations.
Step 5: Create measure(s)
Pct same gender =
DIVIDE (
[Sales Amount],
CALCULATE (
SUM ( SalesNoCustomer[Total Sales] ),
ALL ( CustomerAttributes ),
VALUES ( CustomerAttributes[Gender] ),
)
)Pct same gender Generic =
DIVIDE (
[Sales Amount],
CALCULATE (
[Sales Amount],
ALL ( Customer ),
VALUES ( Customer[Gender] )
)
)| Gender | Sales Amount | Pct same gender Generic |
| Female | 300 | 100.0% |
| — Australia | 100 | 33.3% |
| — Europe | 100 | 33.3% |
| — North America | 100 | 33.3% |
| Male | 300 | 100.0% |
| — Australia | 100 | 33.3% |
| — Europe | 100 | 33.3% |
| — North America | 100 | 33.3% |
| Total | 600 | 100.0% |
| Gender | Sales Amount | Pct same gender Generic |
| Female | 200 | 66.6% |
| — Australia | 100 | 33.3% |
| — Europe | 100 | 33.3% |
| Male | 300 | 66.6% |
| — Australia | 100 | 33.3% |
| — Europe | 100 | 33.3% |
| Total | 600 | 66.6% |
