Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello i am relatively new to PowerBI and need some help
I have 3 columns that have dates in them simillar to example below. How can i create One slicer to see every completion in October for example?
X completion | Y Completion | Z Completion |
10/1/2018 | 11/1/2018 | 10/15/2018 |
Solved! Go to Solution.
You may try using CALCULATE Function to change the context.
https://www.sqlbi.com/articles/filter-arguments-in-calculate/
Hi @nmeliasp
You could create a Calendar table with a relationship for example to Table[X Completion].
The you create a simple measure:
[Completions]= COUNT(Table[X Completion])
Use a slicer with the month column from Calendar and select the month you want.
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
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] ) )
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.
Here is an example of what i am trying to calculate. This would be a DATEDIFF between Completion X and Completion Y, however these columns were melted into an attribute->value columns when i did an unpivot action on them. Hope this helps...
Completion X | Completion Y | Completion Z | Cycle Time X-Y | Cycle Time Y-Z |
10/1/2018 | 11/1/2018 | 12/1/2018 | 31 | 30 |
10/2/2018 | 11/12/2018 | 12/2/2018 | 41 | 20 |
10/3/2018 | 11/3/2018 | 12/3/2018 | 31 | 30 |
10/4/2018 | 11/14/2018 | 12/4/2018 | 41 | 20 |
10/5/2018 | 11/5/2018 | 12/4/2018 | 31 | 29 |
10/6/2018 | 11/16/2018 | 12/4/2018 | 41 | 18 |
10/7/2018 | 11/7/2018 | 12/4/2018 | 31 | 27 |
So you already have it. A DATEDIFF and that is it. What is the problem?
my problem now is i did an unpivot action and now my columns look like this
Completion Type | Completion Date |
Completion X | 10/1/2018 |
Completion Y | 11/1/2018 |
Completion Z | 12/1/2018 |
I am trying to pivot these columns back but having trouble retainig the values. Is there a different way to calcuate the DATEDIFFs between these completion types withouth having to pivot the columns back?
You may try using CALCULATE Function to change the context.
https://www.sqlbi.com/articles/filter-arguments-in-calculate/
User | Count |
---|---|
134 | |
74 | |
72 | |
58 | |
54 |
User | Count |
---|---|
194 | |
95 | |
63 | |
63 | |
51 |