Forum Discussion
Dynamic Column calculation based on Slicer Selection
I'm fairly new to power bi but I've done some prior work in Tableau. In my Tableau reports, I was able to create a calculated field (I called it "Period"), which, based on a dropdown selection, would translate a date into its date, week, month, quarter, or year value. I would then use the calculated field as the column header in their matrix.
The end result is that by simply selecting daily/weekly/monthly/quarterly/yearly from a dropdown, the user would change the matrix to display data by the selected period breakdown.
Does anyone know how to do this in Power BI?
Dragging any date into a visual will create a date hierarchy, as long as its a date format.
or alternatively it is best practise to create a date table and then you can also create your own hierarchies.
but to start simply drag your date that is in a date format onto your visual object and then you can toggle between the date without a hierarchy and the date with. The default date hiearchy is a y/q/m/d so if you want week its best to add a date table into your pbix and then create a relationship between your date and the date table.
this is one way of creating the date table
https://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/
in the picture below you can see i dragged my date onto my axis and it gives me the option to choose between the hierarchy and the date.
when you use the hierarchy you can use the drill down to navigate through the hierarchy you will find it in the top left of the visual.
2 Replies
Dragging any date into a visual will create a date hierarchy, as long as its a date format.
or alternatively it is best practise to create a date table and then you can also create your own hierarchies.
but to start simply drag your date that is in a date format onto your visual object and then you can toggle between the date without a hierarchy and the date with. The default date hiearchy is a y/q/m/d so if you want week its best to add a date table into your pbix and then create a relationship between your date and the date table.
this is one way of creating the date table
https://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/
in the picture below you can see i dragged my date onto my axis and it gives me the option to choose between the hierarchy and the date.
when you use the hierarchy you can use the drill down to navigate through the hierarchy you will find it in the top left of the visual.
In Power BI, it's not supported to directly translate the date part for a date. You need to create calculated columns and use DAX function to get those date parts.
If you want to have the different wise appeared on matrix header, you can populate a date hierarchy in New Matrix preview, and drill down to different level.
If you want to filter the data based on different level (day, month, quarter, etc), you can try custom visual Hierarchy Slicer.
Regards,
