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/
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
Hi,
Share some data and also show the expected result.
I need to calculate the number of days between the different completion types
Ffor example:
Z Completion - X Completion
10/15/2018 - 10/1/2018
goal is to measure cycle times for these three completion types
You need to be less ambiguous describing what you want. I do not understand it.
A sample of the data model would also be helpful.
Well, all the info for calculating the differences is in what we did before. I don't know how you want to show it.
You could, if the number of types of completions is low, have hard-coded measures like:
[Compl_X]= CALCULATE(COUNT(Table[Type_of_Completion ]);Table[Type_of_Completion="X")
[Compl_Y]=CALCULATE(COUNT(Table[Type_of_Completion ]);Table[Type_of_Completion="Y")
and then create other measures simply with the difference.
Another option, although probably overly complicated for what you need, would be to add an additional column to the table shown earlier that's just a copy of Type_of_Completion:
Date Type_of_Completion Type_of_Completion_Filter
10/01/18 X X
11/01/18 Y Y
10/15/18 Z Z
You can then have have Type_of_Completion in rows of the matrix, Type_of_Completion_Filter in a slicer and by selecting one of X, Y or Z on the slicer you choose which one you ant to subtract from those in the rows. This would require a measure like this
Measure_Diff = CALCULATE ( COUNT ( Table[Type_of_Completion] ), ALL ( Table[Type_of_Completion_Filter] ) ) - CALCULATE ( COUNT ( Table[Type_of_Completion_Filter] ), ALL ( Table[Type_of_Completion] ) )
