Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kumshan450
Helper III
Helper III

DAX measure to generate rows based on two dates of a particular column

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. 

 

Employeewisetraveldate.png

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

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]
)

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-yulgu-msft
Employee
Employee

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]
)

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.