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.
I am attempting to sort a slicer by chronological order, however, no matter what I read or try I can't seem to get it to display on the slicer correctly. I brought in a date table and create a query that displays only the Month(Jan, Feb, Mar) and the Month Number (1, 2, 3). I created an additional column in my main table to handle the month number, linked the relationship and sort on month number. Selected the MonthOrder query and I am using that for the Slicer, however, it is still displaying in alpha order, even though the query is sorted on the Month number. What am I doing wrong?
Solved! Go to Solution.
start with DATE field not MONTH
DON'T DRAG ANYTHING ELSE
grab the first field Date from your table DateQry <= this is your date table
look again at my previous instructions
Hello,
We can achieve this by doing below steps.
1. Create a new column in your table by clicking on new column option.
Month= MONTH('Date'[Date])
2. Create a new column in the same table
MonthName= format('Date'[Date],"mmmm")
3. Use a slicer and drag MonthName field. It will show the month names in alphabetical order
4. Now, Select the Visual(Slicer with MonthName) and also select the field from the table(MonthName). We can find option of 'Sort by Column' in Modelling tab of Home Ribbon.
NOTE: If we don't select the MonthName field in the table, this option will be disabled.
In that, Select the Month(Which we created at step 1) field as our sorting option.
Then, we can see the month names in our slicer visual in chronological order.
@kfschaefer I don't know if I completly understand your scenerio, but it sounds like your pulling out the month and creating a seperate table and trying to sort it there...
Typically in your main date table you have your month name and month number. You would just sort your month name by the month number and things should work accordingly. You may be having issues because you are breaking things out and the relationships between the tables aren't holding a sort as expected...
I may be reading this wrong, but that's what I have based on your description.
I did create a separate query that sorts it correctly, and I set up the relationships - month number to month number, sort in asc order on month number. but when I try to use this query/table as the sorce for the slicer, I am unable to display the Month name in month number order.
As you can see I appear to have set everything up, however Having issue with establishing relationship. What am I missing?
Create a table visualization - Drag date field from your date table to Values
make sure it shows date hierarchy - click the Xs by all except Month - convert to slicer
hOk, I tried other methods sitll without success. Do you have a sample that works or know where I can find one?
vz=visualization
Steps Taken.
1. Created vz
2. Drag MonthName, and Month number from Main Data source,
3. Sorted by Month number - displayed correctly as vz
4. However, when I change vz to Slicer the sorting seems to disappear.
5. Created Hierarchy containing Month Number(M#) and Name, set sort asc on M# as vz.
6. Changed vz - Slicer and again Displays Month but not in sorted order.
What am I missing.
start with DATE field not MONTH
DON'T DRAG ANYTHING ELSE
grab the first field Date from your table DateQry <= this is your date table
look again at my previous instructions
Ok Thanks for all your assistance. I am getting closer. I now have a slicer that displays the Month only in the correct order. I added the Month field to the Visual and it will not filter the records. What am I missing?
Also what does this mean: When I try to join the two month fields.
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |