Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
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 ?
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
@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
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
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
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.
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
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 ?
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.
Hello @Ashish_Mathur ,
I tried like you said, or did it wrong, can you explain me with screenshot pls
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.
I take data from database in Direct Query... if i share the file it will be imported column
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.
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
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...
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
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
Click on Id column in the Visializations pane and set "Count" summarizaion as active
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |