Forum Discussion
Multiple Date Column slicer
You may try using CALCULATE Function to change the context.
https://www.sqlbi.com/articles/filter-arguments-in-calculate/
i would like to specify a month for example October and see completions from X Completion Column and Z Completion Column. Not sure if your solution willl work for that.
Let's see if I understand what you're after. In the table you've depicted, what would be the result then for October, 2? OR do you want separate results for each column X, Y, Z?
i would like to seperate the results for each column
Ok, you can have one measure for each column following what I described earlier although that would imply having
a relationship between Calendar and your data table for each column, which is cumbersome if the number of columns is high. Additionally, you would need to use USERELATIONSHIP to activate the relationships as in:
[Completions X]= CALCULATE(COUNT(Table[X Completion]),
USERELATIONSHIP(Table[X Completion],Calendar[Date]))
In any case, the way your table is organised is not the most convenient. Probably best to rearrange it in a more "processable" way so that you have something like:
Date Type_of_Completion
10/01/18 X
11/01/18 Y
10/15/18 Z
You can do this easily in the query editor with the Pivot and Unpivot operations.
With this table you would create a sole relationship between Calendar[Date] and Table[Date] and then you can use the measure
COUNT(Table[Type_of_Completion ])
with [Type_of_Completion] in rows of your matrix and the slicer for the month. Make sense?
This is a great start thank you. Now my next task is being able to measure the differences between these columns. is there a way to have an unpivoted version and a pivoted column? I find measuring the difference between te different completion columns is easier when each completion type is a column unless i'm missing something