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.
i have a data where i need to find out dates where employee is in a particular location.
As data in not in the required structure. require some methods to make it in the required structure.
Solved! Go to Solution.
Hi @Kumshan450 ,
Please new a calculated table.
Output = SELECTCOLUMNS ( FILTER ( CROSSJOIN ( SUMMARIZE ( 'employee travel', 'employee travel'[Employee], 'employee travel'[Visit], "startdate", MIN ( 'employee travel'[R-To date] ), "enddate", MAX ( 'employee travel'[R-To date] ) ), CALENDAR ( MIN ( 'employee travel'[R-To date] ), MAX ( 'employee travel'[R-To date] ) ) ), [Date] >= [startdate] && [Date] <= [enddate] ), "Employee", [Employee], "Visit", [Visit], "Date of Visit", [Date] )
Best regards,
Yuliana Gu
Hi @Kumshan450 ,
Please new a calculated table.
Output = SELECTCOLUMNS ( FILTER ( CROSSJOIN ( SUMMARIZE ( 'employee travel', 'employee travel'[Employee], 'employee travel'[Visit], "startdate", MIN ( 'employee travel'[R-To date] ), "enddate", MAX ( 'employee travel'[R-To date] ) ), CALENDAR ( MIN ( 'employee travel'[R-To date] ), MAX ( 'employee travel'[R-To date] ) ) ), [Date] >= [startdate] && [Date] <= [enddate] ), "Employee", [Employee], "Visit", [Visit], "Date of Visit", [Date] )
Best regards,
Yuliana Gu
I would look at the GENERATE series of functions. These Quick Measures might assist:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365
I would think that you are going to need to create a new calculated table for this.
Hi I need to create a series of rows in a table not dax measures.
I have created calender table and also connected the same to my employee travel data.
Problem is the data is not in the required structure format.
I need to make the same in the required format as mentioned in the output.
Correct, and I am saying that you want to create a calculated TABLE using DAX with something like GENERATE. DAX can be used to create tables as well as measures and calculated columns. You go to Modeling | New Table and type in the formula.
You cannot insert rows into an existing table in DAX, that is now allowed. You have to create a new table that has all of the rows that you want in it from the get go.
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 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |