Forum Discussion

dhruvip23's avatar
dhruvip23
Regular Visitor
2 years ago
Solved

Importing data for specific dates for Azure Analysis Service Database

Hello everyone,   I am trying to import the last 7 years worth of data from a database with an Azure Analysis Service Database connection. It has data from 1900 and kind of large in size and takes ...
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi dhruvip23 ,

    What about passing filters like this? Assuming that the table does not contain dates, consider filtering out the KEY by other containing dates first, and then filtering the table using the KEY.

    EVALUATE
VAR __keys =
    CALCULATETABLE (
        VALUES ( 'OtherTablehasDate'[FieldName] ),
        'OtherTablehasDate'[Date]
            >= DATE ( YEAR ( TODAY () ) - 7, MONTH ( TODAY () ), DAY ( TODAY () ) )
            && 'OtherTablehasDate'[Date] <= TODAY ()
    )
VAR __result = CALCULATETABLE('Table',TREATAS(__keys,'Table'[FieldName]))
RETURN
    __result

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

dhruvip23's avatar
dhruvip23
Regular Visitor
2 years ago

Hey Gao,

 

Thank you so much for this, it works as I expected it to be. Now, the only issue I've got is the table I want to bring in doesn't have any date table. Althogh I've applied the DAX to the other table and it worked out but how do I add that date column into my cuurent table. Many thanks.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi dhruvip23 ,

What about passing filters like this? Assuming that the table does not contain dates, consider filtering out the KEY by other containing dates first, and then filtering the table using the KEY.

EVALUATE
VAR __keys =
    CALCULATETABLE (
        VALUES ( 'OtherTablehasDate'[FieldName] ),
        'OtherTablehasDate'[Date]
            >= DATE ( YEAR ( TODAY () ) - 7, MONTH ( TODAY () ), DAY ( TODAY () ) )
            && 'OtherTablehasDate'[Date] <= TODAY ()
    )
VAR __result = CALCULATETABLE('Table',TREATAS(__keys,'Table'[FieldName]))
RETURN
    __result

 

Best Regards,
Gao
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

  • dhruvip23's avatar
    dhruvip23
    Regular Visitor
    2 years ago

    Thanks a lot Gao it worked as I wanted it to be. Awesome work mate