Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

X-axis for Line chart

Hi PBI experts

 

I was hoping if I can get some help here.

 

The requirement is to replicate the below Excel into PBI report.

 

Datasets, measures all sorted. I need help with X axis.

 

The way it is set up in excel - only DD-MMM is on the x-axis and the lines indicate current year, previous year, previous to previous year. The measures for these years are all set. Just not getting the X-Axis.

 

 

Excell_Daily_Interactivechart.PNG

 

If I add a DD-MMM column, then it takes it as Char and shows continous like this -

 

PBI_Daily_Interactivechart.PNG

 

Concern above is that it will need scrolling. 😞

 

If I use a proper date - then it will show the year of the date, which will be most likely current year. This will "confuse" the users.

 

Any help is much appreciated.

 

Thank you

 

1 ACCEPTED SOLUTION

Hi @Anonymous 

The code is to build a calendar table with all column we need directly. If you have a calendar table, you can add some calculated columns. Column name Rank/NewRank/.../Groups are column names in each calculated column code. Copy each column code and replace _T/_T1/.../T4 as your calendar table name. Then you can get result.

Calendar table:

1.png

Calculated columns :

 

Rank = RANKX(FILTER('Table',[Year] = EARLIER([Year])),[Date],,ASC) 
NewRank = IF(MOD([Year],4) = 0,[RANK],IF([Month]<=2,[RANK],[RANK]+1))
A = IF(MOD([NewRank],4) = 0,INT([NewRank]/4)-1,INT([NewRank]/4))
Group = MINX(FILTER('Table',[A] = EARLIER([A])),[DD-MMM])

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

 

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

Bump. I really appreciate any help here.

Hi @Anonymous 

If you want to combine four days as one in your X-axis, try to add a date flag.

Here I build a calendar table, and relate this table with data table by date column.

Calendar Table = 
VAR _T = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]),"DD-MMM",FORMAT([Date],"DD-MMM"))
VAR _T2 = ADDCOLUMNS(_T,"RANK",RANKX(FILTER(_T,[Year] = EARLIER([Year])),[Date],,ASC))
VAR _T3 = ADDCOLUMNS(_T2,"NewRank",IF(MOD([Year],4) = 0,[RANK],IF([Month]<=2,[RANK],[RANK]+1)))
VAR _T4 = ADDCOLUMNS(_T3,"A",IF(MOD([NewRank],4) = 0,INT([NewRank]/4)-1,INT([NewRank]/4)))
VAR _T5 = ADDCOLUMNS(_T4,"GROUP",MINX(FILTER(_T4,[A] = EARLIER([A])),[DD-MMM]))
RETURN
_T5

Here is the calendar table, we can see in it four days are a group. Sort Group column by A, and use it as X axis in line chart.

1.png

Result:

1.png

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

 

Anonymous
Not applicable

This is fantastic @v-rzhou-msft . I think I'm almsot there.

 

Can I trouble you and ask - what if I already have a Calendar table then how do I convert the workings here to a calculated column?

Or if yo can explain your workings in plain English I will give it a shot.

 

I can see that you are ranking the dates, also considering for the Leap year. And then lookup the min date that falls for the Mod calculated.

 

I've not worked with Add_Columns - if I use the formula after naming of the column, that should help me with calculated column, right?

 

Thank you so much.

Hi @Anonymous 

The code is to build a calendar table with all column we need directly. If you have a calendar table, you can add some calculated columns. Column name Rank/NewRank/.../Groups are column names in each calculated column code. Copy each column code and replace _T/_T1/.../T4 as your calendar table name. Then you can get result.

Calendar table:

1.png

Calculated columns :

 

Rank = RANKX(FILTER('Table',[Year] = EARLIER([Year])),[Date],,ASC) 
NewRank = IF(MOD([Year],4) = 0,[RANK],IF([Month]<=2,[RANK],[RANK]+1))
A = IF(MOD([NewRank],4) = 0,INT([NewRank]/4)-1,INT([NewRank]/4))
Group = MINX(FILTER('Table',[A] = EARLIER([A])),[DD-MMM])

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

 

Anonymous
Not applicable

Legend thy name!!!!

 

Thank you so much @v-rzhou-msft. Absolute legend!

 

 

amitchandak
Super User
Super User

@Anonymous , the continuous axis is only supported Date and number.

You can play with max category width, MAx size, padding, etc to make sure it is no scrolling.

 

As of now, there is no way to make char as continuous ( there is an option in the new version, but reverts for text/char)

 

Anonymous
Not applicable

Thanks @amitchandak 

We tried all those options. Still the same result. We cannot fit 90 points in 1 graph. 360/4=90 

Combining 4 days in 1. That's how Excel has it.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.