Forum Discussion

ducky's avatar
ducky
Icon for Advocate I rankAdvocate I
3 years ago
Solved

Paginated Reports (report builder) - select only records between dates

Hi all,

 

New on paginated reports. 

heres my situation - got a model published on services. I connect to that dataset with PBI Report builder. 

I have a employee table looking like this: 

Employee Idstart dateend datecompany id
A1231/1/20211/31/2021bla
A1232/1/20215/1/2021bla
B1115/2/20211/31/2022bla
B1112/1/20215/1/2021bla
    

 

my table contains the historised employee data where periods are not overlapping. 

I want to have a parameter that user can select a date and I pass this parameter in my query and select only the record of each employee that is in that period (eg: date selector: 2/15/2021 would give 2nd record of each employee)

 

I've made this query but for some reason it doesnt work. 

EVALUATE
SUMMARIZECOLUMNS(
'employee'[company_id],
'employee'[department],
'employee'[employee_name],
'employee'[employee_id],
'employee'[start_dt],
'employee'[end_dt],
FILTER( 'employee',
@date_selector >= Format('employee'[start_dt],"yyyy-mm-dd") && @date_selector <= FORMAT('employee'[END_DT],"yyyy-mm-dd") ))
ORDER BY
'employee'[company_id] ASC

 

Any ideas ? or maybe some direction where I could find an answer? 

 

PS: I've been trying other methods and searching online but no satisfactory results( some due performance) 

Need Help
  • ducky's avatar
    ducky
    3 years ago

    Sahir_Maharaj  - Thanks for your suggestions. However I was more looking for a practical approach to solving my issue. 

    Here's what I finally made out to work using DAx studio - only pasting the filtering solution. 

         FILTER(
         VALUES('employee'[END_DT]), 
          @date_selector <= 'employee'[END_DT]
        ),
        FILTER(
         VALUES('employee'[START_DT]), 
          @date_selector >= 'employee'[START_DT]
        )
        )

2 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    3 years ago

    Hello ducky,

     

    Here are some suggestions:

     

    1. Ensure that the format of the date parameter @date_selector matches the format of the start_dt and end_dt columns in the 'employee' table.

     

    2. Verify that the date comparison in the FILTER function is evaluating correctly. Instead of using the FORMAT function within the FILTER, you can directly compare the dates. Here's the adjusted query:

     

    EVALUATE
SUMMARIZECOLUMNS(
    'employee'[company_id],
    'employee'[department],
    'employee'[employee_name],
    'employee'[employee_id],
    'employee'[start_dt],
    'employee'[end_dt],
    FILTER(
        'employee',
        @date_selector >= 'employee'[start_dt] && @date_selector <= 'employee'[end_dt]
    )
)
ORDER BY 'employee'[company_id] ASC

     

    3. Ensure that there is a proper relationship established between the 'employee' table and any other relevant tables.

     

    Should you require further details or assistance please do not hesitate to reach out to me.

  • ducky's avatar
    ducky
    Icon for Advocate I rankAdvocate I
    3 years ago

    Sahir_Maharaj  - Thanks for your suggestions. However I was more looking for a practical approach to solving my issue. 

    Here's what I finally made out to work using DAx studio - only pasting the filtering solution. 

         FILTER(
         VALUES('employee'[END_DT]), 
          @date_selector <= 'employee'[END_DT]
        ),
        FILTER(
         VALUES('employee'[START_DT]), 
          @date_selector >= 'employee'[START_DT]
        )
        )