Forum Discussion
Not able to cross filter data between Dimension and FACT table
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.
Super UserHi 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?
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.
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.