Forum Discussion
altered filter context for entire visualization (or dynamic column)
I am sure this is a FAQ somewhere but in all of my research, I keep coming back to the same confusion. Hope this helps others as well. I can't quite tell if this is possible.
I want to dynamically mark each underlying row of data being fed into a visualization with a flag to include it or not include it in the visual. That flag is determined at runtime based on a chosen value in a disconnected slicer, so my understanding is that using calculated columns or tables are not options since they are static.
(I think) Since measures are calculated in an aggregating filter context, making this flag using a measure doesn't work either, as it calculates the flag "too late" in that the data is already summarized/grouped and THEN spits out the calculation for the flag (and thus, for trying to mark a record as include/exclude based on a record by record date is forced into some sort of MAX(date) type of calculation which aggregates all of the dates for that block of data).
So for data such as the left table below, the idea is to have a dynamic "column" that is basically (and dynamically), "if(date between jan 2 and jan 3, 1, 0)" that a filter could evaluate to see which records to include when calculating measures.
As it stands, if the visualization groups by category, there would be four underlying rows per category...so a summing measure would count them all (top right in image). I want to filter that to only the two rows per category that apply before calculating measures (bottom right). This is standard fare if using a filter panel filter that is hard coded...the underlying DAX just adjusts the filter context for the table expressions driving the data out to the visualization...just don't see how to do that with a dynamic filter.
I realize I can just create a measure to specifically sum over the adjusted context and get that bottom right result. But if I have many columns, it just seems like I want to recontextualize the entire visual, just like one does by applying a visual level filter using the filter pane...so it FEELS like it should be a thing that is no big deal...but I'm missing something if it is.
(Seems like DAX can be written to do this; are there ways to write DAX table expressions and feed them to a custom visual, by the way?)
BACKGROUND:
(in case this is useful)
I'm building dynamic visuals based on a calculated date range from someone choosing a single date (e.g. a week's worth of data ending on <pick date>).
Most examples of this suggest creating a "filtering" measure that is a 1 if a filter condition is true and 0 otherwise. Since these are measures, they have to be aggregated at whatever grouping level (so fine if you are wanting to only display data for certain months and the visualization rolls up at the month level).
I create a disconnected table with dates, a selectedvalue measure, and a slicer to make the selection. Then, a measure on the table to indicate if a date has been selected. All of that works fine.
If I build visuals that don't aggregate (show all rows), the flag gets set fine. As soon as aggregation happens (where measures are born), it of course doesn't work.
1 Reply
- MFelixSuper User
Hi 1christianhall ,
Not really sure if I have picked up your needs but to what I can understand you want to filter / slice the information based on a selection of a single date and then pick up the full month of that date, or the full week is this correct?
In this case what you need to have is a calendar table with the different columns you need, usually a date table includes date, month, year, week, end of month, and so on check this two links ( DAX table , Power Query )
Then just make your calculations using the corresponding column that you need to have context for example if you want to return the full month of a specific date you can add the FILTER syntax with the End of Month column that is on your calendar table.
Has you refer you can also create a measure to filter out the values based on the seletion and return a measure with 1 and 0 (no need for columns) and make it filter your visualizations has you need, in this case you would need to make use of a COUNT and DATESBETWEEN for filter or a summarize.
If this is not what you want can you please share some additional information?