Say you run a series of events and want to display registration, attendance etc on a bar chart in Power BI. The length of the event names and limitations on the Power BI naming fields in bar charts mean that you can't use the name on the Y-axis, and date gives you problems as well. So you go for a variation on the date worked out in a calculated field during the Power Query part of the process.
This is all fine until the day you run two events on the same day. Now Power BI thinks this is just one event. So you need to find someway of telling the difference between the two events, preferably with a reference to the date of the event.
A way to do this is to add a further calculated column using DAX. This will append an a & b (etc.) onto the date field when there's a clash, but be blank otherwise. Here's the DAX to do that where
St.Date =VAR distinctlycounting = COUNTROWS(FILTER(Titles,Titles[Date]=EARLIER(Titles[Date])))VAR ReIndex = CALCULATE(DISTINCTCOUNT(Titles[Title]),FILTER(Titles,Titles[Date]=EARLIER(Titles[Date])&&Titles[Title]<=EARLIER(Titles[Title])))RETURN IF(distinctlycounting = 1,Titles[Start Date],SWITCH(ReIndex,1, [Start Date] & "a",2, [Start Date] & "b",3, [Start Date] & "c",4, [Start Date] & "d",5, [Start Date] & "e",6, [Start Date] & "f",7, [Start Date] & "g",8, [Start Date] & "h"))
Where the table is called "Titles", the date field is called "Date" and "Start Date" is the calculated field in Power Query.
Obviously you can add more letter variations if you have more events in a single day, or do away with that bit altogether if you just want to use numbers (and you can also get rid of the CONVERT function wrap around as well.
