Not able to cross filter data between Dimension and FACT table

[Pawanw511]

Hello Eveyone

I have one dimension table Account and Fact Table Amount 
We are using snowflake and we have applied RLS on snowflake side on FACT table 
Suppose I have access to 2 account, I can see data for only those two 

when I do simple table visual , I can see two rows

But when I want to add new column total sum of all rows, I see all the Account 

I use below DAX to calculate TotalAmount

TotalAmount =
CALCULATE(
sum(fact_amount[amnt])
, ALLSELECTED(dim_account[account_combo])
)

 
Which function I can use to show only values from dimension table which we get it from joining fact table ?

[v-hashadapu]

Hi @Pawanw511 , We are closing this thread as we haven't heard from you in a while, according to our follow-up policy. If you have any more questions, please start a new thread on the Microsoft Fabric Community Forum. We will be happy to assist you! Thank you for being part of the community!

[v-hashadapu]

Hi @Pawanw511 , Hope your issue is solved. If it is, please consider marking the answer 'Accept as solution', so others with similar issues may find it easily. If it isn't, please share the details.
Thank you.

[v-hashadapu]

Hi @Pawanw511 , Hope your issue is solved. If it is, please consider marking the answer 'Accept as solution', so others with similar issues may find it easily. If it isn't, please share the details.
Thank you.

[v-hashadapu · Accepted solution]

Hi @Pawanw511 , Thank you for reaching out to the Microsoft Community Forum.

Please try below:

TotalAmount =

CALCULATE(

    SUM(fact_amount[amnt]),

    ALL(fact_amount)

)

If this helped solve the issue, please consider marking it “Accept as Solution” so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[v-hashadapu]

Hi @Pawanw511 ,Thank you for reaching out to Microsoft Fabric Community Forum.

1. The issue you're encountering is that using ALLSELECTED(dim_account[account_combo]) is causing the removal of filters from the dim_account table and therefore returning the sum for all accounts instead of just the ones the user has access to.
2. To fix this and ensure that the TotalAmount calculation respects the RLS (Row Level Security) filters applied to both the dim_account and fact_amount tables, you should consider using the ALL function on the appropriate table while retaining the filters from the other tables.

Please try this:

TotalAmount =

CALCULATE(

    SUM(fact_amount[amnt]),

    REMOVEFILTERS(fact_amount),

    FILTER(

        ALL(dim_account),

        dim_account[account_combo] IN VALUES(dim_account[account_combo])

    )

)

3. REMOVEFILTERS(fact_amount) ensures that the sum calculation respects only the filters in the dim_account table and the context of the user’s access.
4. The combination of ALL(dim_account) and the FILTER function ensures that we’re only considering accounts visible to the user and that those accounts are filtered correctly based on the RLS rules.

If this post helps, please mark it ‘Accept as Solution’, so others with similar queries may find it more easily. If not please share the details.

[Pawanw511]

@v-hashadapu 
thanks for reply 
with this solution, rows filtered out but the TotalAmount value and Amount value is same

[danextian]

Hi @Pawanw511 

ALLSELECTED removes filters only from the specified table or column but respects other filters from the visual so in your case it is applied to all visible rows of account_combo column and it will return the same value for all rows for that column. It is normally use if you want to compare the current row value with the value for all selected rows. Why the need to use ALLSELECTED when a regular sum would just return the total value at the total row?

[Pawanw511]

@danextian 
Thank you for your quick reply
I want to use total sum beacuase, I want to do further calculation like percentage of total, Rank, parito ratio.
but want to show data only for 2 account which I have access to based on RLS applied on FACT table
Do you have any other idea by other option I can achive this?

[danextian]

Those other measures should normally return blank for those outside RLS. It's just that, you added an ALLSELECTED measure  in the viz so all rows are visible. Alternatively, you can filter your viz form the  filter pane to show only those with amount.

[Pawanw511]

@danextian 
Account are not fixed , it can be dyamically changed based on user role
there may be chance that for some account amount could be blank

If i filter it out , I will loose account for which blank row is there

[danextian]

You might have misread my reply.

Alternatively, you can filter your viz form the filter pane to show only those with amount.

Amount <> blank.