Getting duplicate entries of a date on the x-axis of a bar graph instead of just one unique entry

[historiantech87]

Hi everyone. I have a simple table with two columns for roll call. One is a date column that just contains standard date entries (12/20/2023). There are multiple rows with the same date since a row was entered for every person that joined the meeting for that date. The other is a name column. Not everyone always puts their name in so there are some blanks and the same name might pop up multiple times since this is a table that contains all the different meetings.

I'm trying to create a bar graph that has unique dates (for each meeting) on the X axis and then the count of names for that date (so ignore the rows that have a date but no name). However, instead I get duplicate dates on the X axis and tried messing around with it but can't figure it out. Anyone have any idea?

[Ashish_Mathur · Accepted solution]

Hi,

Perhaps the reason is that there are time stamps along with the dates thereby making each date_time entry a unique entry.  In the Query Editor, split the column into 2 (Date and Time).  Create a Calendar Table and build a relationship (Many to One and Single) from the Date column (created from the split) of the Data Table to the Date column of the Calendar Table.  To your visual, drag Date from the Calendar Table.

Hope this helps.

[Anonymous · Accepted solution]

Hi @historiantech87 ,

1.Since it won't open your link. Based on your description, I created this data.

2. Creating Visual Objects. You can try to change the category of the x-axis as shown below, in my test, when I changed the category from Continuous to Categorical, there were no more duplicate dates on the x-axis.

Because in power bi, if you use dates or numbers as axes, if you select Continuous, the PBI fits the entire scale on the available axes, but may not show every point (e.g., it may not show all month labels, or every 500 on a scale from 1 to 3000). If you select Sort, each point is displayed, but this may result in scroll bars if you do not have enough space.Detailed information can be found in the documentation:Customize X-axis and Y-axis properties - Power BI | Microsoft Learn

If your Current Period does not refer to this, please clarify in a follow-up reply.

^{Best Regards,}

^{Clara Gong}

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

[Daniel29195]

@historiantech87 

can you share a screenshot of what you are getting . 

best regards

[historiantech87]

Yeah, here's a link to the screenshot. As you can see, on the X axis there are duplicate entries for the same date when I just want one for each unique date.

https://postimg.cc/D4z88smM

[Ashish_Mathur · Accepted solution]

Hi,

Perhaps the reason is that there are time stamps along with the dates thereby making each date_time entry a unique entry.  In the Query Editor, split the column into 2 (Date and Time).  Create a Calendar Table and build a relationship (Many to One and Single) from the Date column (created from the split) of the Data Table to the Date column of the Calendar Table.  To your visual, drag Date from the Calendar Table.

Hope this helps.