Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
hello and thank you in advance for any help offered.
I've searched through the forums and found a lot of solutions but none of them have worked. The ones I've tried are listed below
I am using DirectQuery data for my mode and I have created a DateKey table and created a relationship between that and my data table. The data is a view from SQL. In my DateKey table I have a column created as
YnMnAsInteger = (DateKey[Year])&FORMAT(DateKey[Date],"MM")
The relationship is between DOI_Date and MnYn = FORMAT(DateKey[Date],"MM")&"/" &DateKey[Year].
When I choose Sort by Column | YnMnAsInteger nothing changes.
I'm not sure what I'm missing.
Solved! Go to Solution.
In your table, Month-year should have been sorted or MonthyearInt or Sort column.
In YYYYMM format.
Also, the month year which uses in the report/visual should come from the same table as a sort column.
Create a month sort column and mark it as sort column
@amitchandak, I have a column setup already, MonthY. I added that to the Axis entry along with the DOI_Date from the data table and again nothing changed.
In the relationship screen there is a warning message, "This relationship has cardinality Many-Many. This should...." it won't let me change it. Could that be causing my issue?
David
Yes. Avoid many-to-many unless you really need it. Bridge Tables generally work better for most use cases, and avoid problems M2M can cause.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@edhans & @amitchandak , Ok, so I updated the view in SQL and added the full date colum and created a new EHS_Date column in my DataKey table to match the formatting between the two tables. The relationship screen allowed me to create a many to one relationship between the tables. But this did not change the behaviour. I have to entries in the DataKey table that are technically the same but the data is derived differently. Sort is created with Sort = FORMAT([Date],"YYYYMM") and YnMnAsInteger is created with YnMnAsInteger = (DateKey[Year])&FORMAT(DateKey[Date],"MM"). They produce the same value but they act differently. If I use sort under Axis and Sort By Column it arranges the colums as 04/2018, 06/2018, 03/2018... If I use YnMnAsInteger it sorts the columns as 01/2018, 01/2019, 01/2020, 02/2018, 02/2019, 02/2020...
I can output the view to a csv file and include the pbix if you think that'll help.
Again, thank you for the help.
Hi @boyddt_mn ,
We can create the sort column in fact table as following, then make [DOI_Date] column sort by it to meet your requirement:
Sort = YEAR([DOI])*100+MONTH([DOI])
Best regards,
In your table, Month-year should have been sorted or MonthyearInt or Sort column.
In YYYYMM format.
Also, the month year which uses in the report/visual should come from the same table as a sort column.
Hi @amitchandak,
I have done the soring based on short month year but still not getting this sorted correctly in my visual report.
TIA
You might want to start a new thread @mamunabdullah since this one was solved over a year ago, and provide some data, not just "it isn't working." For example, I cannot see what you are sorting by, or how you have the chart sorted.
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@amitchandak & @edhans , I am going to mark this as the accepted solution. I rang a co-worker and did a screen share with him and he pointed out that I had to force the table to sort by the "Sort" field and @amitchandak stated this here. I just didn't realize what was being requested until I had my co-worker on the line. Thank you both for all of your help.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@amitchandak, OK, so I created a sort column in the SQL view, refreshed the data and used DOI_Date and the new Sort column from the view and same thing. Switched to DOI and it appeared to work but since it was each day the graph wasn't clear attachment included. What is interesting is if Sort is first in the Axis selection and DOI is in 2nd place the graph works as expected. The labels are off since it is presenting a integer instead of a date but I have things moving in the right direction.
@amitchandak, per your suggestion I pulled both the Sort column (Sort = FORMAT([Date],"YYYYMM")) and the MonthY column (
Make sure your visual is sorted by the date field you want. Often it is sorted by the data values. So sort by MM/YYYY field, have the date field sorted by your YYYYMM column, then ensure the visual is actually sorting on the MM/YYYY text field you have. Use the Visual Elipses menu to verify how it is sorted.
As a slight aside, if you can push most of this logic back into Power Query your model will perform better. Calculated Columns can increase the model size and slow it down compared to native data. I use the MM/YYYY sort technique but in Power Query, not with a FORMAT() function. See this article for more.
If you still cannot get it to sort right, can you share the PBIX via OneDrive?
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@edhans , I won't be able to share it until this evening. My company lock down access to cloud storage so I'll have to email the file to myself and share it from my home computer. Should I out put the results of the view and include it as a CSV file? As per my last post I'm getting close but missing some pieces.
That should be ok, but please do NOT share confidential data your employer doesn't want you to see. If you are nervous about it, you can PM me the link and only I will see it. I will not be able to tinker with it until this evening (about 7 hrs away for me) though.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@edhans, I got pulled away from this project and am just now getting back to it. I'm going to play with it some more and if I cannot figure it out I'll get you the files this evening.
David
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |