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:
Summary:
*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
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:
| Date | Sale method |
| 25/01/2018 | Internet |
| 27/01/2018 | Store |
| 27/02/2018 | Store |
| ... | ... |
Summary:
| Date | Number of Sales* |
| Jan 18 | 2 |
| Feb 18 | 1 |
| ... | ... |
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
