Forum Discussion
Date filter selection issue - graphs with different need
Thanks PaulDBrown . I tried a few approaches and managed to solve the issue with one table using disconnected Date-table and a measure to filter the original date table using selected value (A, below). However, I have several tables and graphs with different values (MTD, YTD, %-differences etc). If I would use the same approach for all tables I would need to re-create 50+ measures.
I hoped that I could use a measure to filter a whole table (with several values). However, I didnt work as planned (B, below).
A - My approach below (solution for one table with one value):
-----------------------------------------------------------------
1. Measure (all months in selected Month-Year)
2. Result (selecting Aug 2019 and I get all months during 2019)
B - My failed approach to use a measure filter to filter a whole table/graph with several values:
---------------------------------------------------------------------
1. Using measure in the visual filter (returing 1 if table should show the corresponding MonthYears)
I thought I could create a visual filter using a measure, where the measure return "1" if the MonthsYears are included in the selected set (same logic as before, selecting Aug2019 should return Jan-Dec during 2019 of MonthYear). However, I dont receive any values.
Please let me know if you see any errors or have a smarter approach. Would too much work to create 50+ measures to filter some tables with 10+ values.
Many thanks
Anonymous
A common technique is to use a disconnected period table. Check out this thread in which the OP was after something very similar:
Thanks PaulDBrown . I tried a few approaches and managed to solve the issue with one table using disconnected Date-table and a measure to filter the original date table using selected value (A, below). However, I have several tables and graphs with different values (MTD, YTD, %-differences etc). If I would use the same approach for all tables I would need to re-create 50+ measures.
I hoped that I could use a measure to filter a whole table (with several values). However, I didnt work as planned (B, below).
A - My approach below (solution for one table with one value):
-----------------------------------------------------------------
1. Measure (all months in selected Month-Year)
2. Result (selecting Aug 2019 and I get all months during 2019)
B - My failed approach to use a measure filter to filter a whole table/graph with several values:
---------------------------------------------------------------------
1. Using measure in the visual filter (returing 1 if table should show the corresponding MonthYears)
I thought I could create a visual filter using a measure, where the measure return "1" if the MonthsYears are included in the selected set (same logic as before, selecting Aug2019 should return Jan-Dec during 2019 of MonthYear). However, I dont receive any values.
Please let me know if you see any errors or have a smarter approach. Would too much work to create 50+ measures to filter some tables with 10+ values.
Many thanks
Anyone that could help me on this? Still stuck on finding an efficient solution to all my graphs?
Thanks
Anonymous
I like your solution to filtering the table using the filter pane: very easy and efficient.
As regards your quest to find an easy way to use your other measures, I am not aware of a way which does not involve writing new measures.
HOWEVER....
There may be a less onerous way, albeit it nevertheless still involves writing new measures...
There is an (arguably) slightly obscure FUNCTION in DAX which can come to the rescue for these kind of challenges; TREATAS.
This function (which was released relatively recently) in effect establishes a virtual relationship between two disconnected tables based on a common field. I have tested the function against a couple of time intelligence functions working on a date table linked to a fact table and it "appears" to work smoothly. Here is an example:
The model looks like this:
Based on this, I have created a number of measures using the Date Table as the filter context:
For example:
MTD Actuals = CALCULATE([Sum Actuals]; DATESMTD('Date Table'[date]))or:
PYMTD Actuals = CALCULATE([MTD Actuals]; DATEADD('Date Table'[date]; -1;YEAR))or just a simple average, which responds to the Date Table filter context due to the relationship established in the model:
Average Actuals = AVERAGE('Fact Table'[Actuals])We can then include these measures in a new CALCULATE function using the TREATAS function to filter using the unrelated period table:
(Following the order of the measures above)
MTD Actuals (TREATAS) = CALCULATE([MTD Actuals]; TREATAS(VALUES('YearMonth Table'[Month Year]); 'Date Table'[Month Year]))PYMTD Actuals (TREATAS) = CALCULATE([PYMTD Actuals]; TREATAS(VALUES('YearMonth Table'[Month Year]); 'Date Table'[Month Year]))Average Actuals (TREATAS) = CALCULATE([Average Actuals]; TREATAS(VALUES('YearMonth Table'[Month Year]); 'Date Table'[Month Year]))Which gives you this:
To conclude...
It doesn't solve your problem in the sense that you still have to write the measures. However, it does make life slightly easier in that you can write the new measures with a simple copy and paste + changing the target measure in the CALCULATE function.
New Measures (TREATAS) = CALCULATE([Choose target measure];
TREATAS(VALUES('YearMonth Table'[Month Year]); 'Date Table'[Month Year]))I hope it helps.