{"id":238,"date":"2023-11-21T15:34:49","date_gmt":"2023-11-21T14:34:49","guid":{"rendered":"https:\/\/bitwise.exposed\/?p=238"},"modified":"2026-03-16T09:38:48","modified_gmt":"2026-03-16T08:38:48","slug":"accurate-percentages-with-row-level-security","status":"publish","type":"post","link":"https:\/\/bitwise.exposed\/index.php\/2023\/11\/21\/accurate-percentages-with-row-level-security\/","title":{"rendered":"Accurate percentages with row-level security"},"content":{"rendered":"\n

Abstract, from a SQLBI article. Link to the original article on SQLBI.com click here https:\/\/sql.bi\/817079<\/a>. 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.<\/p>\n\n\n\n

Pattern to compute ratios, when data is hidden by row-level security.<\/p>\n\n\n\n

Pattern 1<\/h5>\n\n\n\n

Full row-level security<\/p>\n\n\n\n

Example Measure<\/p>\n\n\n\n

DAX<\/span><\/path><\/path><\/svg><\/span>
Pct<\/span> <\/span>over<\/span> <\/span>All<\/span> =<\/span><\/span>\nDIVIDE<\/span> (<\/span><\/span>\n    <\/span>[Sales Amount]<\/span>,<\/span><\/span>\n    <\/span>CALCULATE<\/span> (<\/span><\/span>\n        <\/span>[Sales Amount]<\/span>,<\/span><\/span>\n        <\/span>ALL<\/span> ( <\/span>Customer<\/span> )<\/span><\/span>\n    )<\/span><\/span>\n)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
No row level security applied:<\/p>\n\n\n\n
Continent<\/strong><\/td>Sales Amount<\/strong><\/td>Pct over All<\/strong><\/td><\/tr>
Australia<\/td>100<\/td>33.3%<\/td><\/tr>
Europe<\/td>100<\/td>33.3%<\/td><\/tr>
North America<\/td>100<\/td>33.3%<\/td><\/tr>
Total<\/td>300<\/strong><\/td>Total<\/strong><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Row-level security: „Austrlia, Europe“<\/p>\n\n\n\n
Continent<\/strong><\/td>Sales Amount<\/strong><\/td>Pct over All<\/strong><\/td><\/tr>
Australia<\/td>100<\/td>50.0%<\/td><\/tr>
Europe<\/td>100<\/td>50.0%<\/td><\/tr>
Total<\/strong><\/td>200<\/strong><\/td>100.0%<\/strong><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
If that the goal to achieve, everything is fine.<\/p>\n\n\n\n
Pattern 2<\/h5>\n\n\n\n
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.<\/p>\n\n\n\n
DAX<\/span><\/path><\/path><\/svg><\/span>
GrandTotalSales<\/span> = <\/span>ROW<\/span> ( <\/span>"Grand Total Sales"<\/span>, <\/span>[Sales Amount]<\/span> )<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
<\/td>Grand Total Sales<\/strong><\/td><\/tr>
<\/td>300<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
This table will be not affected by row-level security setting.<\/p>\n\n\n\n
To provide more granularity, calculated table could look like this.<\/p>\n\n\n\n
DAX<\/span><\/path><\/path><\/svg><\/span>
SalesNoCustomer<\/span> =<\/span><\/span>\nADDCOLUMNS<\/span> (<\/span><\/span>\n    <\/span>SUMMARIZE<\/span> (<\/span><\/span>\n        <\/span>Sales<\/span>,<\/span><\/span>\n        <\/span>'Product'<\/span>[ProductKey]<\/span>,<\/span><\/span>\n        <\/span>'Date'<\/span>[Date]<\/span>,<\/span><\/span>\n        <\/span>Store<\/span>[StoreKey]<\/span><\/span>\n    ),<\/span><\/span>\n    <\/span>"Total Sales"<\/span>, <\/span>[Sales Amount]<\/span><\/span>\n)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
In that case it’s also necessary to create the propper relations.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Measure not affected by row-level security<\/p>\n\n\n\n
DAX<\/span><\/path><\/path><\/svg><\/span>
Pct<\/span> <\/span>Not<\/span> <\/span>Secured<\/span> =<\/span><\/span>\nDIVIDE<\/span> (<\/span><\/span>\n    <\/span>[Sales Amount]<\/span>,<\/span><\/span>\n    <\/span>SUM<\/span> ( <\/span>SalesNoCustomer<\/span>[Total Sales]<\/span> )<\/span><\/span>\n)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Continent<\/strong><\/td>Sales Amount<\/strong><\/td>Pct over All<\/strong><\/td>Pct Not Secured<\/strong><\/td><\/tr>
Australia<\/td>100<\/td>50.0%<\/td>33.3%<\/td><\/tr>
Europe<\/td>100<\/td>50.0%<\/td>33.3%<\/td><\/tr>
Total<\/td>200<\/strong><\/td>100.0%<\/strong><\/td>66.6%<\/strong><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Pattern 3<\/h5>\n\n\n\n
Partially exclude DimensionAttributes from row-level security.<\/p>\n\n\n\n
Step 1:<\/strong> CalculatedTable, here CustomerAttributes<\/p>\n\n\n\n
DAX<\/span><\/path><\/path><\/svg><\/span>
CustomerAttributes<\/span> =<\/span><\/span>\nVAR<\/span> <\/span>T<\/span> =<\/span><\/span>\n    <\/span>SUMMARIZE<\/span> (<\/span><\/span>\n        <\/span>Customer<\/span>,<\/span><\/span>\n        <\/span>Customer<\/span>[Country]<\/span>,<\/span><\/span>\n        <\/span>Customer<\/span>[State]<\/span>,<\/span><\/span>\n        <\/span>Customer<\/span>[Continent]<\/span>,<\/span><\/span>\n        <\/span>Customer<\/span>[Gender]<\/span><\/span>\n    )<\/span><\/span>\nRETURN<\/span><\/span>\n    <\/span>ADDCOLUMNS<\/span> ( <\/span>T<\/span>, <\/span>"CustomerAttributeKey"<\/span>, <\/span>ROWNUMBER<\/span> ( <\/span>T<\/span> ) )<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
<\/td>Country<\/td>State<\/td>Continent<\/td>Gender<\/td>CustomerAttributeKey<\/td><\/tr>
<\/td>xxx<\/td>xxx<\/td>xxx<\/td>xxx<\/td>1<\/mark><\/td><\/tr>
<\/td>xxx<\/td>xxx<\/td>xxx<\/td>xxx<\/td>2<\/mark><\/td><\/tr>
<\/td>xxx<\/td>xxx<\/td>xxx<\/td>xxx<\/td>3<\/mark><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Step 2: <\/strong>Calculated Column, here SalesTable<\/p>\n\n\n\n
DAX<\/span><\/path><\/path><\/svg><\/span>
CustomerAttributeKey<\/span> =<\/span><\/span>\nLOOKUPVALUE<\/span> (<\/span><\/span>\n    <\/span>CustomerAttributes<\/span>[CustomerAttributeKey]<\/span>,<\/span><\/span>\n    <\/span>CustomerAttributes<\/span>[Country]<\/span>,    <\/span>RELATED<\/span> ( <\/span>Customer<\/span>[Country]<\/span> ),<\/span><\/span>\n    <\/span>CustomerAttributes<\/span>[State]<\/span>,      <\/span>RELATED<\/span> ( <\/span>Customer<\/span>[State]<\/span> ),<\/span><\/span>\n    <\/span>CustomerAttributes<\/span>[Continent]<\/span>,  <\/span>RELATED<\/span> ( <\/span>Customer<\/span>[Continent]<\/span> ),<\/span><\/span>\n    <\/span>CustomerAttributes<\/span>[Gender]<\/span>,     <\/span>RELATED<\/span> ( <\/span>Customer<\/span>[Gender]<\/span> )<\/span><\/span>\n)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Step 3:<\/strong> Propagate, CalcCol from Step 2 to SalesNoCustomers<\/p>\n\n\n\n
DAX<\/span><\/path><\/path><\/svg><\/span>
SalesNoCustomer<\/span> =<\/span><\/span>\nADDCOLUMNS<\/span> (<\/span><\/span>\n    <\/span>SUMMARIZE<\/span> (<\/span><\/span>\n        <\/span>Sales<\/span>,<\/span><\/span>\n        <\/span>Sales<\/span>[CustomerAttributeKey]<\/span>,<\/span><\/span>\n        <\/span>'Product'<\/span>[ProductKey]<\/span>,<\/span><\/span>\n        <\/span>'Date'<\/span>[Date]<\/span>,<\/span><\/span>\n        <\/span>Store<\/span>[StoreKey]<\/span><\/span>\n    ),<\/span><\/span>\n    <\/span>"Total Sales"<\/span>, <\/span>[Sales Amount]<\/span><\/span>\n)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Step 4:<\/strong> In that case it’s also necessary to create the propper relations.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Step 5:<\/strong> Create measure(s)<\/p>\n\n\n\n
DAX<\/span><\/path><\/path><\/svg><\/span>
Pct<\/span> <\/span>same<\/span> <\/span>gender<\/span> =<\/span><\/span>\nDIVIDE<\/span> (<\/span><\/span>\n    <\/span>[Sales Amount]<\/span>,<\/span><\/span>\n    <\/span>CALCULATE<\/span> (<\/span><\/span>\n        <\/span>SUM<\/span> ( <\/span>SalesNoCustomer<\/span>[Total Sales]<\/span> ),<\/span><\/span>\n        <\/span>ALL<\/span> ( <\/span>CustomerAttributes<\/span> ),<\/span><\/span>\n        <\/span>VALUES<\/span> ( <\/span>CustomerAttributes<\/span>[Gender]<\/span> ),<\/span><\/span>\n     )<\/span><\/span>\n)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
DAX<\/span><\/path><\/path><\/svg><\/span>
Pct<\/span> <\/span>same<\/span> <\/span>gender<\/span> <\/span>Generic<\/span> =<\/span><\/span>\nDIVIDE<\/span> (<\/span><\/span>\n    <\/span>[Sales Amount]<\/span>,<\/span><\/span>\n    <\/span>CALCULATE<\/span> (<\/span><\/span>\n        <\/span>[Sales Amount]<\/span>,<\/span><\/span>\n        <\/span>ALL<\/span> ( <\/span>Customer<\/span> ),<\/span><\/span>\n        <\/span>VALUES<\/span> ( <\/span>Customer<\/span>[Gender]<\/span> )<\/span><\/span>\n    )<\/span><\/span>\n)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Gender<\/td>Sales Amount<\/td>Pct same gender Generic<\/td><\/tr>
Female<\/strong><\/td>300<\/strong><\/td>100.0%<\/strong><\/td><\/tr>
— Australia<\/td>100<\/td>33.3%<\/td><\/tr>
— Europe<\/td>100<\/td>33.3%<\/td><\/tr>
— North America<\/td>100<\/td>33.3%<\/td><\/tr>
Male<\/strong><\/td>300<\/strong><\/td>100.0%<\/strong><\/td><\/tr>
— Australia<\/td>100<\/td>33.3%<\/td><\/tr>
— Europe<\/td>100<\/td>33.3%<\/td><\/tr>
— North America<\/td>100<\/td>33.3%<\/td><\/tr>
Total<\/strong><\/td>600<\/strong><\/td>100.0%<\/strong><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Gender<\/td>Sales Amount<\/td>Pct same gender Generic<\/td><\/tr>
Female<\/strong><\/td>200<\/strong><\/td>66.6%<\/strong><\/td><\/tr>
— Australia<\/td>100<\/td>33.3%<\/td><\/tr>
— Europe<\/td>100<\/td>33.3%<\/td><\/tr>
Male<\/strong><\/td>300<\/strong><\/td>66.6%<\/strong><\/td><\/tr>
— Australia<\/td>100<\/td>33.3%<\/td><\/tr>
— Europe<\/td>100<\/td>33.3%<\/td><\/tr>
Total<\/strong><\/td>600<\/strong><\/td>66.6%<\/strong><\/td><\/tr><\/tbody><\/table><\/figure>\n","protected":false},"excerpt":{"rendered":"
Pattern to compute ratios, when data is hidden by row-level security.<\/p>\n","protected":false},"author":1,"featured_media":248,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7,16,18],"tags":[],"class_list":["post-238","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-dax","category-pattern","category-row-level-security"],"_links":{"self":[{"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/posts\/238","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/comments?post=238"}],"version-history":[{"count":8,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/posts\/238\/revisions"}],"predecessor-version":[{"id":249,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/posts\/238\/revisions\/249"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/media\/248"}],"wp:attachment":[{"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/media?parent=238"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/categories?post=238"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/tags?post=238"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}