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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Rustin788
Frequent Visitor

Month Order / Multiple Tables

Hey,

 

I'm setting up a new report.  For this report, I get the daily values from a simple dataset that just has (Year, Month, SKU, Cases Sold) that updates daily.  I have another data set that has information on the SKU's (description,color, images, etc).  There are more SKUs in the data set than I need so the SKU table filters those down for me. 

 

I can't figure out how to get my data sets to properly go from January to December.  I have a fiscal calendar table setup that I use for other things, but for this report, I need to be connected to the SKU table.  

 

DataSet1: Year, Month (As "Jan"), SKU, QTY   (Shows 2023 & 2024 data) (Year and Month are set as TEXT)

DataSet2: SKU, Color, Description, Image...

 

What I've tried:

- Adding in an index column and sorting by index

- Used a dax function to change the months (written as "JAN" to just "1") Tried "1" as both text and a number

 

I'm working with line chart, matrix, and tables.

 

If needed I can try and have someone adjust the raw data report I get, but since I don't have direct access to that I would like to try and fix on my end first.

 

Thanks for any help!

1 ACCEPTED SOLUTION

Just to confirm, you have a FACT table that has Year, Month, SKU and QTY; you have a DIM table for SKU that has SKU, Color, Description, etc., and you have a date table, correct? If this is accurate, you will need to have a bridge table to connect your dim date table to your fact table. The bridge table would have year and month and a concatenation to be the key. You would need the same concatenation on your DIM Date and your fact table so that you could join the fact to the bridge table. 

 

For your ordering on the months, instead of using a calculated column, go into Power Query and add a conditional column (if Month = Jan then 1, add rule and if Month = Feb then 2, etc.). Then, in Power BI select Month and sort by the month number column you added in Power Query. Or, if you switch to the Date Reference table from SQLBI that I talk about in the blog, it already has month number as a field that can be used to sort Month Name.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

6 REPLIES 6
Rustin788
Frequent Visitor

@audreygerred 

 

I think because I used the below DAX to create the month number field it is giving me a circular dependency error.

 

FullMonth =
    SWITCH(TRUE(),
        CONTAINSSTRING(Roll_Towel[Month], "Jan"), "1",
        CONTAINSSTRING(Roll_Towel[Month], "Feb"), "2",
        CONTAINSSTRING(Roll_Towel[Month], "Mar"), "3",
        CONTAINSSTRING(Roll_Towel[Month], "Apr"), "4",
        CONTAINSSTRING(Roll_Towel[Month], "May"), "5",
        CONTAINSSTRING(Roll_Towel[Month], "Jun"), "6",
        CONTAINSSTRING(Roll_Towel[Month], "Jul"), "7",
        CONTAINSSTRING(Roll_Towel[Month], "Aug"), "8",
        CONTAINSSTRING(Roll_Towel[Month], "Sep"), "9",
        CONTAINSSTRING(Roll_Towel[Month], "Oct"), "10",
        CONTAINSSTRING(Roll_Towel[Month], "Nov"), "11",
        CONTAINSSTRING(Roll_Towel[Month], "Dec"), "12",
        BLANK()
    )

Ideally you will have a date table and the month number is just part of the table. Here is a blog post I have about the date reference table I use: http://powerbiwithme.com/2023/08/01/the-custom-date-table-edition/




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





I have a date table I use.  When I try to mark it as a date table it tells me the date column can't have dates.  My date table has Year, Fiscal Period, Fiscal Month, Fiscal Quarter, and Date and goes from 1/1/22 - 12/30/24 (The last day of our 2024 fiscal year).

 

If  I am able to set that as the date table, would I be able to use the dates there and still connect to my other SKU reference table?

Just to confirm, you have a FACT table that has Year, Month, SKU and QTY; you have a DIM table for SKU that has SKU, Color, Description, etc., and you have a date table, correct? If this is accurate, you will need to have a bridge table to connect your dim date table to your fact table. The bridge table would have year and month and a concatenation to be the key. You would need the same concatenation on your DIM Date and your fact table so that you could join the fact to the bridge table. 

 

For your ordering on the months, instead of using a calculated column, go into Power Query and add a conditional column (if Month = Jan then 1, add rule and if Month = Feb then 2, etc.). Then, in Power BI select Month and sort by the month number column you added in Power Query. Or, if you switch to the Date Reference table from SQLBI that I talk about in the blog, it already has month number as a field that can be used to sort Month Name.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





So sorry, I got pulled into something else and had to work on that for a bit.  Yes, your table descriptions sound accurate.  I will see what I can do with creating a bridge table.

audreygerred
Super User
Super User

Have a column with month number set to number and then go to your month name field and go to the Column Tools ribbon that appears, click on Sort by column and select month number. This will sort your month name by month number. Sort one column by another column in Power BI - Power BI | Microsoft Learn




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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