Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello!
I have one table that contains 9 columns, 7 of which are dates. They are dates of an enrolment process. I need to create a visual (bar chart) that shows the count of the dates for each column for a definied period. I want to filter the visual using one date slicer.
This is the table:
The visual I want to create needs to look something like the below:
What is the best method for inserting one date slicer to filter all the results? For example, I may want to see the activity of just one week in a month, or the last 3 months, 6 months etc.
Thank you!
Solved! Go to Solution.
@Anonymous this is what you need to do, which will take care of everything.
Unpivot your table, in query editor, select your school column and right click, and select upivot other columns
It will add two column, one attribute which will be your category and other column value which will be your dates
Apply the changes, add calendar dimension in your model (many blog posts are there).
On bar chart, use date/month/year on x-axis from calendar table, legend will be your attribute and value will be count of rows from your table.
Now you can slicer and dice the data by date or by category.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi,
A good way to do this, would be to remodel your data.
Try modelling your date columns to a single data column.
Example:
ID | Date | DateType |
123 | 08/08/12 | Leaving Date |
123 | 09/08/12 | Registered Date |
123 | 10/08/12 | Enquire Date |
This way you'll be able to do a (Distinct)Count on ID, use DateType as Legend and Date as Axis.
Let me know if you need any more help
sounds like you need to related a date/time table for each of your dates and then a master date/time table related to each one of those date/time tables. Then you set your slicer on the month field of the master time table and use that month in the visualization.
Wouldn't that work?
whoops. You can't have more than one active relationship on a field. So the unpivot method suggested by the other posters is the correct solution.
@Anonymous this is what you need to do, which will take care of everything.
Unpivot your table, in query editor, select your school column and right click, and select upivot other columns
It will add two column, one attribute which will be your category and other column value which will be your dates
Apply the changes, add calendar dimension in your model (many blog posts are there).
On bar chart, use date/month/year on x-axis from calendar table, legend will be your attribute and value will be count of rows from your table.
Now you can slicer and dice the data by date or by category.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thanks very much! This seems to be doing the trick!
I do have an error message for two of the columns though:
DataSource.Error: Microsoft SQL: The type of column "txtWithdrawnDate" conflicts with the type of other columns specified in the UNPIVOT list.
Details:
DataSourceKind=SQL
DataSourcePath=xxx
Message=The type of column "txtWithdrawnDate" conflicts with the type of other columns specified in the UNPIVOT list.
Number=8167
Class=16
I have checked that the column type is Date/Time which it is. Do you know how I could solve this?
Many thanks, much appreciated!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |