topic Re: Change visual sorting based on weeks in Developer

Change visual sorting based on weeks

jaryszek — Mon, 23 Jun 2025 07:34:06 GMT

hi Guys,

I have visual which should be sorted from Thuesday, not Friday (visual by WeekDays. WeekOfMonth: Week 1, Week 2, Week 3, Week 4-->WeekDay).

This is the order which i want to show based on week:

So it is starting from Tuesday in Week 1, in Week 3 is Wednesday.

How to sort WeekDay by specific Week and number?
I tried with this column:

but I am getting :

How to solve it?

In other words:

By changing Weeks in slicer i want to see on Visual sorted WeekDays according to calendar order (from first in week until last):

Best,
Jacek

Re: Change visual sorting based on weeks

v-dineshya — Mon, 23 Jun 2025 09:55:50 GMT

Hi @jaryszek ,

Thank you for reaching out to the Microsoft Community Forum.

You want your visual to show weekdays in correct calendar order for each WeekOfMonth based on actual date sequence, not default weekday names or alphabetically.

Please follow below steps to fix the issue.

1. Created table (Table) with below M code in Query editor.

let
Source = Table.FromRows({
{"2025-04-01", "Tuesday", "Week 1"},
{"2025-04-02", "Wednesday", "Week 1"},
{"2025-04-03", "Thursday", "Week 1"},
{"2025-04-04", "Friday", "Week 1"},
{"2025-04-05", "Saturday", "Week 1"},
{"2025-04-06", "Sunday", "Week 1"},
{"2025-04-07", "Monday", "Week 2"},
{"2025-04-08", "Tuesday", "Week 2"},
{"2025-04-09", "Wednesday", "Week 2"},
{"2025-04-10", "Thursday", "Week 2"},
{"2025-04-11", "Friday", "Week 2"},
{"2025-04-12", "Saturday", "Week 2"},
{"2025-04-13", "Sunday", "Week 2"}
}, {"Date", "WeekDay", "WeekOfMonth"}),

ChangedTypes = Table.TransformColumnTypes(Source,{
{"Date", type date}, {"WeekDay", type text}, {"WeekOfMonth", type text}
})
in
ChangedTypes

Please refer snap.

2. Created sorting columns in "Table" View. Please refer snap

SortIndex for sorting by date: SortIndex = RANKX(ALL('Table'), 'Table'[Date], , ASC)

Composite key for unique sorting: WeekDay_Composite = 'Table'[WeekOfMonth] & " - " & 'Table'[WeekDay]

DisplayWeekDay = 'Table'[WeekDay]

3. Sorted Composite column. refer in snap.

Select WeekDay_Composite column --> Sort by Column --> choose SortIndex.

Now each WeekDay_Composite ("Week 1 - Tuesday") is sorted by actual date.

4. In visual, Drag the field "WeekDay_Composite" in X-axis and measure "Sum of CostInBillingCurrency" in Y-axis.

Please refer attached PBIX file.

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

Re: Change visual sorting based on weeks

jaryszek — Mon, 23 Jun 2025 10:54:45 GMT

Ok attached example worked.

I tried similar approach with Weeks:
Added WekDayKey:

AddingWeekDayKey = Table.AddColumn( #"Added Adjusted_DayOfWeek_Sort", "WeekDayKey", each [WeekOfMonth] & "-" & Text.From([Day]))

and sort by SortIndex column.

But i do not want to put my WeekDayKey into visual -> only i want to put there weeks like Week1, Week2.
The same for WeekDay_Composite

How to woraround it?

Best,
Jacek

Re: Change visual sorting based on weeks

v-dineshya — Mon, 23 Jun 2025 16:25:54 GMT

Hi @jaryszek ,

You want to sort by a hidden composite key (like WeekDayKey or WeekDay_Composite), but display only WeekDay in the visual and do this dynamically for each selected WeekOfMonth ( Tuesday --> Wednesday for Week 1, Monday --> Tuesday for Week 2, etc).

Please follow below steps.

1. Created a composite label column:

This will be used in the X-axis instead of plain WeekDay:

DisplayLabel = 'Table'[WeekOfMonth] & "-" & 'Table'[WeekDay]

2. Created a numeric sort key

SortIndex = VALUE(FORMAT('Table'[Date], "YYYYMMDD"))

Note: This gives you a unique number for sorting in true calendar order.

3. Sort DisplayLabel by SortIndex

In Data view: Select the DisplayLabel column and Click “Sort by column” --> click SortIndex

Note: Now your labels are sorted correctly in the visual.

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

Re: Change visual sorting based on weeks

jaryszek — Tue, 24 Jun 2025 07:41:22 GMT

Thanks is still not working:

Let me copy my Dim_Table here.
DateDateKeyDayOfYearFiscalQuarterFiscalYearIsHolidayIsWeekendMonthMonthNoMonthShortQueryNoWeekDayWeekNoYearYearMonthYearQuarterDayWeekOfMonthWeekDayNumberWeekOfMonth_SortMinDateInWeekWeekDay_SortDisplayLabelSortIndex

wtorek, 1 kwietnia 2025	20250401	91	2	2025	False	False	April	4	Apr	4	Tuesday	14	2025	2025-04	2	1	Week 1	2	1	wtorek, 1 kwietnia 2025	11	Week 1-Tuesday	20250401
środa, 2 kwietnia 2025	20250402	92	2	2025	False	False	April	4	Apr	4	Wednesday	14	2025	2025-04	2	2	Week 1	3	1	wtorek, 1 kwietnia 2025	12	Week 1-Wednesday	20250402
czwartek, 3 kwietnia 2025	20250403	93	2	2025	False	False	April	4	Apr	4	Thursday	14	2025	2025-04	2	3	Week 1	4	1	wtorek, 1 kwietnia 2025	13	Week 1-Thursday	20250403
piątek, 4 kwietnia 2025	20250404	94	2	2025	False	True	April	4	Apr	4	Friday	14	2025	2025-04	2	4	Week 1	5	1	wtorek, 1 kwietnia 2025	14	Week 1-Friday	20250404
sobota, 5 kwietnia 2025	20250405	95	2	2025	False	True	April	4	Apr	4	Saturday	14	2025	2025-04	2	5	Week 1	6	1	wtorek, 1 kwietnia 2025	15	Week 1-Saturday	20250405
niedziela, 6 kwietnia 2025	20250406	96	2	2025	False	False	April	4	Apr	4	Sunday	14	2025	2025-04	2	6	Week 1	7	1	wtorek, 1 kwietnia 2025	16	Week 1-Sunday	20250406
poniedziałek, 7 kwietnia 2025	20250407	97	2	2025	False	False	April	4	Apr	4	Monday	15	2025	2025-04	2	7	Week 1	1	1	wtorek, 1 kwietnia 2025	17	Week 1-Monday	20250407
wtorek, 8 kwietnia 2025	20250408	98	2	2025	False	False	April	4	Apr	4	Tuesday	15	2025	2025-04	2	8	Week 2	2	2	wtorek, 8 kwietnia 2025	21	Week 2-Tuesday	20250408
środa, 9 kwietnia 2025	20250409	99	2	2025	False	False	April	4	Apr	4	Wednesday	15	2025	2025-04	2	9	Week 2	3	2	wtorek, 8 kwietnia 2025	22	Week 2-Wednesday	20250409
czwartek, 10 kwietnia 2025	20250410	100	2	2025	False	False	April	4	Apr	4	Thursday	15	2025	2025-04	2	10	Week 2	4	2	wtorek, 8 kwietnia 2025	23	Week 2-Thursday	20250410
piątek, 11 kwietnia 2025	20250411	101	2	2025	False	True	April	4	Apr	4	Friday	15	2025	2025-04	2	11	Week 2	5	2	wtorek, 8 kwietnia 2025	24	Week 2-Friday	20250411
sobota, 12 kwietnia 2025	20250412	102	2	2025	False	True	April	4	Apr	4	Saturday	15	2025	2025-04	2	12	Week 2	6	2	wtorek, 8 kwietnia 2025	25	Week 2-Saturday	20250412
niedziela, 13 kwietnia 2025	20250413	103	2	2025	False	False	April	4	Apr	4	Sunday	15	2025	2025-04	2	13	Week 2	7	2	wtorek, 8 kwietnia 2025	26	Week 2-Sunday	20250413
poniedziałek, 14 kwietnia 2025	20250414	104	2	2025	False	False	April	4	Apr	4	Monday	16	2025	2025-04	2	14	Week 2	1	2	wtorek, 8 kwietnia 2025	27	Week 2-Monday	20250414
wtorek, 15 kwietnia 2025	20250415	105	2	2025	False	False	April	4	Apr	4	Tuesday	16	2025	2025-04	2	15	Week 2	2	2	wtorek, 8 kwietnia 2025	21	Week 2-Tuesday	20250415
środa, 16 kwietnia 2025	20250416	106	2	2025	False	False	April	4	Apr	4	Wednesday	16	2025	2025-04	2	16	Week 3	3	3	środa, 16 kwietnia 2025	31	Week 3-Wednesday	20250416
czwartek, 17 kwietnia 2025	20250417	107	2	2025	False	False	April	4	Apr	4	Thursday	16	2025	2025-04	2	17	Week 3	4	3	środa, 16 kwietnia 2025	32	Week 3-Thursday	20250417
piątek, 18 kwietnia 2025	20250418	108	2	2025	False	True	April	4	Apr	4	Friday	16	2025	2025-04	2	18	Week 3	5	3	środa, 16 kwietnia 2025	33	Week 3-Friday	20250418
sobota, 19 kwietnia 2025	20250419	109	2	2025	False	True	April	4	Apr	4	Saturday	16	2025	2025-04	2	19	Week 3	6	3	środa, 16 kwietnia 2025	34	Week 3-Saturday	20250419
niedziela, 20 kwietnia 2025	20250420	110	2	2025	False	False	April	4	Apr	4	Sunday	16	2025	2025-04	2	20	Week 3	7	3	środa, 16 kwietnia 2025	35	Week 3-Sunday	20250420
poniedziałek, 21 kwietnia 2025	20250421	111	2	2025	False	False	April	4	Apr	4	Monday	17	2025	2025-04	2	21	Week 3	1	3	środa, 16 kwietnia 2025	36	Week 3-Monday	20250421
wtorek, 22 kwietnia 2025	20250422	112	2	2025	False	False	April	4	Apr	4	Tuesday	17	2025	2025-04	2	22	Week 3	2	3	środa, 16 kwietnia 2025	37	Week 3-Tuesday	20250422
środa, 23 kwietnia 2025	20250423	113	2	2025	False	False	April	4	Apr	4	Wednesday	17	2025	2025-04	2	23	Week 4	3	4	środa, 23 kwietnia 2025	41	Week 4-Wednesday	20250423
czwartek, 24 kwietnia 2025	20250424	114	2	2025	False	False	April	4	Apr	4	Thursday	17	2025	2025-04	2	24	Week 4	4	4	środa, 23 kwietnia 2025	42	Week 4-Thursday	20250424
piątek, 25 kwietnia 2025	20250425	115	2	2025	False	True	April	4	Apr	4	Friday	17	2025	2025-04	2	25	Week 4	5	4	środa, 23 kwietnia 2025	43	Week 4-Friday	20250425
sobota, 26 kwietnia 2025	20250426	116	2	2025	False	True	April	4	Apr	4	Saturday	17	2025	2025-04	2	26	Week 4	6	4	środa, 23 kwietnia 2025	44	Week 4-Saturday	20250426
niedziela, 27 kwietnia 2025	20250427	117	2	2025	False	False	April	4	Apr	4	Sunday	17	2025	2025-04	2	27	Week 4	7	4	środa, 23 kwietnia 2025	45	Week 4-Sunday	20250427
poniedziałek, 28 kwietnia 2025	20250428	118	2	2025	False	False	April	4	Apr	4	Monday	18	2025	2025-04	2	28	Week 4	1	4	środa, 23 kwietnia 2025	46	Week 4-Monday	20250428
wtorek, 29 kwietnia 2025	20250429	119	2	2025	False	False	April	4	Apr	4	Tuesday	18	2025	2025-04	2	29	Week 4	2	4	środa, 23 kwietnia 2025	47	Week 4-Tuesday	20250429
środa, 30 kwietnia 2025	20250430	120	2	2025	False	False	April	4	Apr	4	Wednesday	18	2025	2025-04	2	30	Week 4	3	4	środa, 23 kwietnia 2025	41	Week 4-Wednesday	20250430

Put this table to chat gpt, write prompt: "make for me a formatted table from it to copy to excel" and you will get my Dim_date table.

Why I am getting this error?

And second question is: How to hidden displayKey on the visual?

Best,
Jacek

Re: Change visual sorting based on weeks

jaryszek — Tue, 24 Jun 2025 08:19:00 GMT

Ok i have managed to change labels using dax like :

DisplayLabelNoRepeats = VAR CurrentLabel = [DisplayLabel] VAR CurrentDate = [DateKey] VAR LabelCount = CALCULATE( COUNTROWS('Dim_Date'), FILTER( 'Dim_Date', [DisplayLabel] = CurrentLabel && [DateKey] <= CurrentDate ) ) RETURN IF(LabelCount = 1, CurrentLabel, CurrentLabel & " " & LabelCount )

and got DisplayLabelNoRepeats column sortd properly.

But why this is shown like here?

I want to only display WeekDay, no created label...
How to hide the column ? (tried Hide in report view but it didnt work).

Best,
Jacek

Re: Change visual sorting based on weeks

v-dineshya — Tue, 24 Jun 2025 09:37:58 GMT

Hi @jaryszek ,

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Do not include sensitive information. Do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided.

Thank you

Re: Change visual sorting based on weeks

jaryszek — Tue, 24 Jun 2025 10:40:50 GMT

I provided data, my dim_table @v-dineshya

You can add another table like Date, Cost and that is all.

How can i upload power bi sample here?

Edit:
just copy my Fct_Cost table from here:
Date KeyCost

20250401	135.27
20250402	142.58
20250403	132.15
20250404	165.42
20250405	178.96
20250406	119.80
20250407	153.77
20250408	144.20
20250409	123.54
20250410	151.62
20250411	138.79
20250412	169.40
20250413	110.95
20250414	159.68
20250415	136.84
20250416	126.21
20250417	158.93
20250418	174.32
20250419	162.08
20250420	118.52
20250421	148.16
20250422	124.75
20250423	139.67
20250424	150.44
20250425	172.51
20250426	180.17
20250427	116.38
20250428	141.90
20250429	130.63
20250430	155.09

ModelView:

I provided 2 tables in order to reproduce the case.

you see the filtering? Week1 should start from Thuesday, not from friday...
Plus i do not want to display labels but only Weekdays...

Best,
Jacek

Re: Change visual sorting based on weeks

v-dineshya — Thu, 26 Jun 2025 08:09:42 GMT

Hi @jaryszek ,

Please refer below PBIX file and output snap for you reference.

If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

Re: Change visual sorting based on weeks

jaryszek — Thu, 26 Jun 2025 08:53:44 GMT

Thank you.

How this is working?
I see you merged tables and filter them somehow...

Best,
Jacek

Re: Change visual sorting based on weeks

v-dineshya — Thu, 26 Jun 2025 09:20:19 GMT

Hi @jaryszek ,

Please follow below steps.

1. In Query editor, I have created new column (Data-Copy) based on
'Date' column in 'Dim_Date' table.

2. I have done some transformation on (Data-Copy) like change the language and datatype, created custom column with weekday values.

3. I have created table (table) with Weekday and Sort columns.
and merged the new table with existing 'Dim_Date' table. and i have expanded the table and removed blank columns.

4. You can see the 'APPLIED STEPS' in Query editor, it will show each and every steps. Please refer below snap.

If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.