Forum Discussion
Matrix change starting row
I would like to show parts produced per hour, starting with 7 AM today and running through 7 AM tommorow, rather than 0 AM today and running through 23 today. Is there a way to do this?
If you only have 24 values - 7am, 8am, 9am, etc for all 24 periods, you could create a custom column in Power Query to do this:
if [Column1] = "7am" then 1 else if [Column1] = "8am" then 2 else if [Column1] = "9am" then 3 else nullThat is 3. You just need to do the other 21. There must be a else null at the end without getting too deep in if/then/else constructs.
It is 100% case sensitive. So "7am" is not the same as "7AM"If you have minutes then this isn't workable like that. Otherwise, we'd need to see data. How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
9 Replies
Yes. You will need to add a column to your data to show how you want Power BI to sort it. So 7am would be 1, 8am would be 2, etc. Then you tell Power BI to sort the time column by your sort column. You can read more about that here.
Note that it must be a 1:1 relationship. So if you have Jan 1, 2020, and 7am, and then Jan 2, 2020, also with 7am, the "1" sort code must be for both dates, assuming the 7am is in a separate column. You cannot have, for example, 1 for the Jan 1 7am record and a 2 for the Jan 2 7am record. Power BI will see a 7am record with a 1 and 2 and will not know how to sort it.
I'm trying to do that but I'm getting a circular reference error message.
Establish your sort by column either in the source data, or in Power Query. You are probably using a Calculated Column. That isn't generally good practice. You can read this article on avoiding circular dependencies, but it goes much deeper than this. I cannot find the article I am specifically looking for right now, but it is on the SQLBI site somewhere.
In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
SQLBI Video on Measures vs Calculated ColumnsEDIT: It was by Marco Russo from SQLBI, but he posted to Twitter, not their site. Here it is.
Yes pabeader - direct query should be avoided unless absolutely necessary. I didn't test any of that against DQ, and it doesn't suprise me it would break it. I do not think most of what I did there will fold.
So many limitations in Direct Query... not the least of which is you miss out on that fantastic tabular engine.
