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 up the whole capacity. What DAX query can I use to only pull a certain amount of data and where do I put it? Thanks for your help

 

 

  • 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

6 Replies

  • foodd's avatar
    foodd
    Community Champion
    2 years ago

    Hello dhruvip23, and thank you for sharing a question with the Community.  Please remember to adhere to the decorum of the Community Forum when asking a question.

    Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then share a file’s URL.

    https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150

    This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

  • vigneshba's avatar
    vigneshba
    Frequent Visitor
    2 years ago

    Hi dhruvip23  - These are my finding, comments and questions related your post.

    1. First of all, we need to understand which AAS tier is being used here. Please refer below link for the size and pricing. https://azure.microsoft.com/en-us/pricing/details/analysis-services/

     

    2. If data size is more the the AAS tier that being used, we will face the memory issue. So we should revisit if AAS tier is enough or need to upgrade the tier

     

    3. We can still optimise the data model to fit into the existing tier but to understand that we need to look into the model or share below information if possible.

    • No of Tables used and respective relationship 
    • Help us to identify which are dimension tables and which one is fact
    • No of Columns and Rows in the each column based on Data Type

     

    Based on the information, we can help more to resolve this issue or provide better way to fix this issue

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi dhruvip23 ,

     

    Try the DAX below. It screens dates from today to seven years ago.

     

    EVALUATE
CALCULATETABLE(
    'YourTableName',
    'YourTableName'[YourDateColumnName] >= DATE(YEAR(TODAY()) - 7, MONTH(TODAY()), DAY(TODAY())) 
    && 'YourTableName'[YourDateColumnName] <= TODAY()
)

     

     

    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