Adopting page filters in a report for calculate DAX expressions

spandence

New Member
Joined
Dec 20, 2019
Messages
1
Office Version
  1. 365
Platform
  1. Windows
  2. Web
Hello


I am facing a problem with filters when using a calculate function. To paraphrase an example, say I have a table of sales with a date and a sale method of either internet or store. In a second table, I would like to create a monthly summary of the number of sales - so I create a date table and summarise the rows to entries of the first of months only.


To summarise, I have two tables as follows:


Sales:
DateSale method
25/01/2018Internet
27/01/2018Store
27/02/2018Store
......

Summary:
DateNumber of Sales*
Jan 182
Feb 181
......
*Number of sales = CALCULATE(COUNTROWS(Sales), DATESINPERIOD(Sales[Date], Summary[Date], 1, MONTH))

The number of sales field calculates fine without the desire for filtering on sales method.


The problem comes with filtering. I would like to have a slicer on the page on the sale method field to give me the option of counting the number of sales for internet, store or combined internet/store, when displaying the summary table fields in visuals. When I add a slicer on the sale method field and I select a distinct value, the calculated field in the summary table does not update.


I have tried looking through all the filtering formulas (CROSSFILTER, USERELATIONSHIP, FILTER, FILTERS etc) and I have got nowhere. I tried setting up a dummy DATATABLE with the internet and store values in it to make a relationship, to no avail. I tried adding a filter to the calculate value above something like:

Number of sales = CALCULATE(COUNTROWS(Sales), DATESINPERIOD(Sales[Date], Summary[Date], 1, MONTH), IF(HASONEFILTER(Sales[Sales method]), Sales[Sales Method] = FILTERS(Sales[Sales method]))

Though I realise this last attempt was hopeless (I was getting desperate!).


Would anybody be able to help with this one, even if to tell me what I'm trying to do is not possible) - I'm going crazy!


Any help is very much appreciated.


Dan
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Forum statistics

Threads
1,223,705
Messages
6,173,985
Members
452,540
Latest member
haasro02

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top