Forum Discussion
filtering using calculated fields
If I understand correctly you want a sort of dynamic measure, with dynamic code, that calculates different things depending on what is selected on slicers.
I would suggest using harvester measures. You create a separate, unconnected table for each of the variables you want to play with, like Category, Year, and if you need it, Type of comparison (>0, =0)
With the harvester measure you capture what's been selected and[Sales1]=CALCULATE(SUM(Report1[Qty]), Report1[Category]=[Harvested Category1]; Report1[Year]=[Harvested Year1])
[Sales2]=CALCULATE(SUM(Report1[Qty]), Report1[Category]=[Harvested Category2]; Report1[Year]=[Harvested Year2])
Table= FILTER(ALL(Report1[Account]), [Sales1] > 0 && [Sales2] =0)
You could follow a similar approach to select the comparison ">0", "=0", etc.
Hi MNGoodyear
How about creating a table
Table= FILTER(ALL(Report1[Account]), [Sales A] > 0 && [Sales B] =0)
with
[Sales A]=CALCULATE(SUM(Report1[Qty]), Report1[Category]="A"))
[Sales B]=CALCULATE(SUM(Report1[Qty]), Report1[Category]="B"))
You can set the year on a slicer
hi the objective is to categorise a number of scenarios, for example adding sliders for :
Category A 2017
Category B 2017
Category A 2018
Category B 2018
so the user can interactively apply different types of filters to the data which i dont think the solution will allow
If I understand correctly you want a sort of dynamic measure, with dynamic code, that calculates different things depending on what is selected on slicers.
I would suggest using harvester measures. You create a separate, unconnected table for each of the variables you want to play with, like Category, Year, and if you need it, Type of comparison (>0, =0)
With the harvester measure you capture what's been selected and[Sales1]=CALCULATE(SUM(Report1[Qty]), Report1[Category]=[Harvested Category1]; Report1[Year]=[Harvested Year1])
[Sales2]=CALCULATE(SUM(Report1[Qty]), Report1[Category]=[Harvested Category2]; Report1[Year]=[Harvested Year2])
Table= FILTER(ALL(Report1[Account]), [Sales1] > 0 && [Sales2] =0)
You could follow a similar approach to select the comparison ">0", "=0", etc.
