DAX remove filter

[Anonymous]

Hi,

I have a page that have all the slicers and they are synchronized with all the reports.

In one of the report I want to remove the date slicer, the selection of date slicer in the first page is by financial week. below is my DAX, but it is not removing the filter:

Cost =

CALCULATE(

     sum('Job'[Cost]),

'Job[JobFlag] =0,

ALL ('Date')

I have also tried below, to remove all the filter except the location, but did not work:

Cost=

CALCULATE(

     sum('Job'[Cost]),

'Job'[JobFlag] =0,

ALLEXCEPT( Location,Location[Location Code] )

)

[d_gosbell · Accepted solution]

---

@Anonymous wrote:

The date slicer is on another page, in this case the edit interaction will not work.

---

Sync'ed slicers actually create an invisible copy of the slicer on the synch'ed pages. If you go into the View menu on the page with the visible slicer you can click on the slicer and use the Sync Slicers pane to temporarily make the slicer visible by ticking the check box under the eye icon.

Then you can edit the interactions on your other page and then change the slicer on your second page back to being hidden.

[Ashish_Mathur]

Hi,

Your first formula of Cost seems correct.  Share the link from where i can download the PBI file.

[Anonymous]

@amitchandak 

My PBIX is using tabular model, not sure if this will be helpful.

[v-lili6-msft]

hi @Anonymous 

Sample pbix file and your expected output would help tremendously.

You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

By the way the filter of a slicer that fields from the 'Job' table won't be removed in this formula. see this post: https://community.powerbi.com/t5/Desktop/percent-of-column-total/m-p/874406#M419278

Regards,

Lin

[Anonymous]

Hi @v-lili6-msft ,

I figured out that the measure value is accurate, i.e. it is removing the filter, but when I bring month column into the matrix, it only shows the the selected month (as below image). I wanted to show all months. I wanted to show all  the months.

[Anonymous]

I think the issue is I am trying to remove the filter from the date table, and at the same time I am using the column from the date table in the visual.

is there a work around that other than using another date table?

[v-lili6-msft]

hi @Anonymous 

If so, you need another date table. you could just duplicate the basic date table.

Then create another relationship as basic date table.

Now use the fields from this new date table in the matrix visual and set "Show items with no data"

If you still have the problem, just show a sample pbix file and your expected output.

Regards,

Lin

[Anonymous]

Thanks guys.

Please let me know if there is another solution.

I have already added the new date table and it is working.

Thanks gain.

[Anonymous]

Hi,

The problem I am facing now is in one of measures I need to compare two columns value if they are equal, so I need to use the FILTER function, if I use the ALL function outside the FILTER function it gives the worng figures, so I need to use the ALL function inside the FILTER function to get the accurate result, but I don't how.

Below is the code that gives the worng figures:

Measure =
VAR CurrentDate = VALUE( FORMAT( DATE(YEAR ( NOW ()), MONTH ( NOW ())-3, DAY(NOW())), "YYYYMMDD"))

RETURN

CALCULATE(
AVERAGE('Job'[Time (Indicators)]),
Filter ( 'Job,
Job[Supp]=0
&& Job[Rework] =0
&& FORMAT(Job[Completed Date], "YYYYMMDD")> "19000101"
&& 'Job'[ime (Indicators)] >0
&& 'Job'[DateLocalLookupKey] >= CurrentDate
&& (Job[Location Code] = Job[Resp Centre] || Job[Hybrid Flag] = 1)
),
All ('Date')
)

[amitchandak]

Using another table workaround should work

[amitchandak]

Try Removefilter or Edit Interactions and block the date filter on that visual

https://docs.microsoft.com/en-us/dax/removefilters-function-dax

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

@amitchandak 

The date slicer is on another page, in this case the edit interaction will not work.

[d_gosbell · Accepted solution]

---

@Anonymous wrote:

The date slicer is on another page, in this case the edit interaction will not work.

---

Sync'ed slicers actually create an invisible copy of the slicer on the synch'ed pages. If you go into the View menu on the page with the visible slicer you can click on the slicer and use the Sync Slicers pane to temporarily make the slicer visible by ticking the check box under the eye icon.

Then you can edit the interactions on your other page and then change the slicer on your second page back to being hidden.

[Anonymous · Accepted solution]

Hi @d_gosbell ,

Thank you so much for this, it worked, such a simple solution but perfect.

Thanks again.

[amitchandak]

Is the date you are filter is from Date table

?

I tried one matrix and seems to have worked.

Sales With all date = CALCULATE(sum(Sales[Sales]),'Item'[Brand]="Apple",all(OrderTime[Order Date]))

[Anonymous]

@amitchandak 

Yes, the date is from date table, and I have tried your code, which similar to mine, but it did not work.

[Anonymous]

The date slicer is on another page, in this case the edit interaction will not work.