Gather same Date instead to have each row

[ItSupportElm]

Hello, 

I have one column "DateCreation" and one column "Id" who correspond to the order.

But in my column there sometime 2,3 or 4 times de same date , i want to gather them. 

 I want to have for exemple "17/02/2020" One line for said i have 4 "Id" (orders)

Thanks for the futur answer, great day

[v-alq-msft]

Hi, @ItSupportElm 

Based on your description, I created data to reproduce your scenario.

You may create a table visual to display the reuslt and drag two columns from 'Table'. Please make sure the aggregation for id is 'Count'.

As a workaround, you may create a measure as follows.

CountForDate = 
CALCULATE(
    COUNTROWS(
        FILTER(
            ALLSELECTED('Table'),
            'Table'[DateCreation]=MAX('Table'[DateCreation])
        )
    )
)

Result:

Best Regards

Allan

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

[ItSupportElm]

Hello thanks for the answer guys.

@v-alq-msft  i tried your solution but it didnt work i dont know why : 

 Do you have any idea why ? 

[v-alq-msft]

Hi, @ItSupportElm 

'CountForDate' is a measure not a calculated column. If possible, please show me your data model. Do mask sensitive data before uploading.

Best Regards

Allan

[ItSupportElm]

@v-alq-msft So, i figured out why it didnt work because, im in direct query, there is a dax formul to change Date time in Date format ?

thanks again

[v-alq-msft]

Hi, @ItSupportElm 

You can also change the data type of the column in Power Query. It is the easiest way to solve it.

Best Regards

Allan

[ItSupportElm]

@v-alq-msft 

I change the format in power query , but it didnt want to display like i want : 

there is formul dax that i can use to change the format of the datetime in date ? please 

[Ashish_Mathur]

Hi,

Write the following calculated column formula to extract Date from a Date/time column

=INT(Data[Date_time])

Format this column as Date.

Hope this helps.

[v-alq-msft]

Hi, @ItSupportElm 

Have you tried my last second reply?  You may create a calculated column and a measure as follows.

calculated column:
DateCreation2 = CatalogueInteractifCommande[DateCreation].[Date]

measure:
CountForDate = 
CALCULATE(
    COUNTROWS(
        FILTER(
            ALLSELECTED('CatalogueInteractifCommande'),
            'CatalogueInteractifCommande'[DateCreation2]=MAX(CatalogueInteractifCommande[DateCreation2])
        )
    )
)

Result:

Best Regards

Allan

[ItSupportElm]

Hello @v-alq-msft ,

It work only with imported column not Directquery.

@Ashish_Mathur I dont understand how to do it ccan you give an exemple that i can apply with my model please ?

[Ashish_Mathur]

Hi,

Click on the Data tab at the extreme left hand side pane.   Select the table and go to Modelling > New column.  Write the formula.

Hope this helps.

[ItSupportElm]

Hello @Ashish_Mathur ,

I tried like you said, or did it wrong, can you explain me with screenshot pls

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  Tell me exactly in which table do you want to create another column.

[ItSupportElm]

I take data from database in Direct Query... if i share the file it will be imported column

[v-alq-msft]

Hi, @ItSupportElm 

The 'id' column is the unique identifier of the table. So when we choose 'Count' for it, it will be 1. I think the issue is that the original data type for DateCreation in Power Query is datetime even though you have changed it to date. Please try the following steps.

You may create a calculated column and a measure as follows.

calculated column:
DateCreation2 = CatalogueInteractifCommande[DateCreation].[Date]

measure:
CountForDate = 
CALCULATE(
    COUNTROWS(
        FILTER(
            ALLSELECTED('CatalogueInteractifCommande'),
            'CatalogueInteractifCommande'[DateCreation2]=MAX(CatalogueInteractifCommande[DateCreation2])
        )
    )
)

Result:

Best Regards

Allan

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

[Pragati11]

Hi @ItSupportElm ,

Check for 2 things:

1. Check that the format of your date column is DATE type not DATETIME type

2. When you move ID column to the visual along with Date column, set Summarisation on ID column to COUNT

Thanks,

Pragati

[ItSupportElm]

Hi @Pragati11, and @az38 

My date column is already in DATE type, then when i followed you second advice it didnt work.

I steal have 4 row with the same date ... 

Actually i want to count the number of date with the date itself... 

[Pragati11]

Hi @ItSupportElm ,

Can you please put a screenshot against what kind of summarization you did on ID column when moved on visual with DATE column?

Thanks,

Pragati

[Pragati11]

Hi @ItSupportElm ,

I have tried the similar scenario at my end and I get the correct output as shown below:

Hope this helps.

Thanks,

Pragati

[az38]

Hi @ItSupportElm 

Click on Id column in the Visializations pane and set "Count" summarizaion as active