Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help, Please, with DAX Date Processing

I’m honing my DAX skills in Power BI Desktop, and I’ve run into a snag that I hope the community can help me with.   I’ve been using Reza Rad’s excellent book, Power BI from Rookie to Rock Star: Bo...
Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi Anonymous , 

    I'm glad to cooperate you to solve the problem. If the answer is helpful, can you please accept it as a solution? Then we are able to close the thread. And more people who have the same request will benefit here.

     

    Best Regards,

    Xue Ding

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

My company doesn't allow the use of DropBox, etc., so Xue Ding was kind enough to allow me to reach out to him offline.  Following is the email to him, to keep the trail intact.

--------------------

Attached is the TEST-3 PBIX.  As a long-time programmer/analyst, though not in PBI, I made a couple of changes in trying to debug this since my original post.  For input, I used the DimCustomer, DimDate5 and FactInternetSales21 sheets in the Microsoft AdventureWorksDW2012.xlsx test file.

 

When I hard-code the dates (in the “Test Date Range” measure), we get the correct results, a portion of which is shown below –

 

CustomerKey

FullName

Total Revenue

Test Date Range

28866

Aaron Adams

117.96

 

20285

Aaron Alexander

69.99

69.99

20075

Aaron Allen

3,399.99

 

17862

Aaron Baker

1,750.98

 

12067

Aaron Bryant

133.96

 

21414

Aaron Butler

14.98

14.98

21151

Aaron Campbell

1,155.48

 

27916

Aaron Carter

39.98

39.98

28187

Aaron Chen

39.98

 

16749

Aaron Coleman

61.96

61.96

27663

Aaron Collins

6,047.32

 

18695

Aaron Diaz

6,029.57

 

19692

Aaron Edwards

94.48

 

25415

Aaron Evans

2,433.04

 

14617

Aaron Flores

1,538.56

 

15566

Aaron Foster

4,912.47

2,482.03

18804

Aaron Gonzales

1,810.46

1,810.46

20123

Aaron Gonzalez

132.97

 

 

 

But, when I use the calculated range (in the “Last Period Revenue” measure), it seems to loop until all memory is gone and then it throws the error.

 

For your convenience, the measures are shown as follows:

 

Test Date Range = CALCULATE(SUM('Subset of FactInternetSales21'[SalesAmount]), DATESBETWEEN(DimDate5[FullDateAlternateKey], DATE(2008,06,01), DATE(2008,07,31)))

 

Last Period Revenue = CALCULATE(SUM('Subset of FactInternetSales21'[SalesAmount]), DATESBETWEEN(DimDate5[FullDateAlternateKey], [First Date In Range], [Last Date In Range]))

 

Both “First Date In Range” and “Last Date In Range” appear to be correct, as they are correctly shown in the Card visuals.

 

I will post the contents of this email, so others may gain from your assistance.

v-xuding-msft's avatar
v-xuding-msft
Community Support
6 years ago

Hi Anonymous ,

I found that the "Subset of FactInternetSales21" table is from "FactInternetSales21" table. And there is about 6w rows in it. The two tables take up lots of memory. And the calculation about "Subset" table causes the report slowly. If you don't need "FactInternetSales21" table, I think you could remove it and create the  "Subset of FactInternetSales21" table in Query Editor directly. If you still need it, you could remove some columns.Otherwise, you could try to connect the data source with DirectQuery mode.  

 

The ways can solve the problem about memory. However the report still runs slowly, the measure takes too long time (about several hours) to respond so that I can't get a result. You could reference the blogs that my first answer mentioned to optimize it. 

 

Hope the answer will help you a little. I have tried my best. 

 

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Kudos are nice too.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thank you so much for all your help, Xue Ding!!

    • v-xuding-msft's avatar
      v-xuding-msft
      Community Support
      6 years ago

      Hi Anonymous , 

      I'm glad to cooperate you to solve the problem. If the answer is helpful, can you please accept it as a solution? Then we are able to close the thread. And more people who have the same request will benefit here.

       

      Best Regards,

      Xue Ding

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        My sincere apologies for the delay, Xue Ding!!

         

        After drastically cutting the "Subset of FactInternetSales21" file, to the 1388 records with the lowest CustomerKeys, it now completes and, while I still have some issues to work on, I think those are ones I can handle.  My main error was in grossly underestimating PBI's use of memory.

         

        Again, my appreciation for your help!!