PowerBI sort by Month Number

[Anonymous]

Hi,

I am trying to arrange my central date table by month number in order to have the line graph display January through December but I keep getting a cardinality error.

I have a central date table with more than one year, therefore I believe that might be the problem.

Could you please advice? 

Thanks

[v-binbinyu-msft · Accepted solution]

Hi @Anonymous ,

Please try below steps:
1.Create a month table, and add a index column , then sort the month column by index

Month_sort:

2.Create a relationship between your month table and fact table

Fact:

Mode:

3.Then add a line chart

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lukiz84]

Can you show me your date table after adding the DateSortColumn?

[Anonymous]

So I have tried your DAX two different ways as I couldn't close brackets. I get the same error for both. 

[lukiz84]

whats the second one? the first is correct and should work, i use the exact same setting in my date table... so it's weird

[Anonymous]

I tried the correct one and it is still giving the same error 😞

I have multiple years, could this be the issue? 

[lukiz84]

no, that's the default case ... can you send the pbx file?

[lukiz84]

Add a DateSortColumn to your Calendar table, like

DateSortColumn := 100 * YEAR(Calendar[Date]) + MONTH(Calendar[Date])

and apply this to your Month-Column as "Sort by".

[Anonymous]

Thanks for the reply. I am getting this error when I try to sort?