Forum Discussion
Ignore Filters from multiple tables in DAX
Hi,
I have a scenario to calculate "Current Month's Orders" whose values should be constant and not change when any filter/slicer from multiple tables is applied. I have four slicers from four different tables and one card visual displaying the "Current Month's Orders" value, If we filter any slicer the card visual value should not change. Can anyone help me to achieve this scenario?
I have created this calculated measure :
ALL Function works for multiple columns from the same table.
But I have to Ignore Filters when applied from three other tables as well like shown in the screenshot below:
In one of the posts, the solution mentioned is to turn off the Edit Interaction of the three slicers with the card. For this to be done, select one slicer then go to the Format tab, click on Edit Interactions then turn it off, and do this for all three slicers.
But I do not see this option. Can someone pls show me the screenshot?
or how to achieve this in DAX?
Anonymous , Try like
CALCULATE(DISTINCTCOUNT('olap fact_Transaction'[order_id]),
FILTER(ALL('olap dim_Date'),[Current_YearMonth]="TRUE"),ALL('olap dim_TransactionType'),ALL('olap dim_Client'))CALCULATE(DISTINCTCOUNT('olap fact_Transaction'[order_id]),
FILTER(ALL('olap dim_Date'),[Current_YearMonth]="TRUE"),ALL('olap dim_TransactionType'),ALL('olap fact_Transaction'),ALL('olap dim_Client'))
4 Replies
- amitchandak
Super User
Anonymous , Try like
CALCULATE(DISTINCTCOUNT('olap fact_Transaction'[order_id]),
FILTER(ALL('olap dim_Date'),[Current_YearMonth]="TRUE"),ALL('olap dim_TransactionType'),ALL('olap dim_Client'))CALCULATE(DISTINCTCOUNT('olap fact_Transaction'[order_id]),
FILTER(ALL('olap dim_Date'),[Current_YearMonth]="TRUE"),ALL('olap dim_TransactionType'),ALL('olap fact_Transaction'),ALL('olap dim_Client'))Hi amitchandak,
This works, thanks a lot 😊 .
- amitchandak
Anonymous , If all is across table have multiple all, better to use all on 'OLTP Fact Transactions'
all each all individually
all(all('OLTP DimeTime') , all('OLTP dim_client')
refer for all and removefilters
https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak
Hi amitchandak ,
Did you mean like this?Current Month's Orders = CALCULATE(DISTINCTCOUNT('olap fact_Transaction'[order_id]),FILTER(ALL(ALL('olap dim_Date'),ALL('olap dim_TransactionType'),ALL('olap fact_Transaction'),ALL('olap dim_Client')),[Current_YearMonth]="TRUE"))But I don't think it's the right syntax. Can you tell me the correct syntax?