Hello Everyone,
I'm still new to power BI and having issues in calculating multiple calculation. I have been using below DAX script to switch between multiple measures.
Revenue Slab =
IF (
HASONEVALUE ('Range'[Range] ),
SWITCH (
VALUES ('Range'[Range] ),
"1. 1 - 199k", calculate(file[Total Revenue], filter(file,and(file[Total Revenue]>0,file[Total Revenue]<200000))),
"2. 200k - 499k", calculate(file[Total Revenue], filter(file,and(file[Total Revenue]>=200000,file[Total Revenue]<500000))),
"3. 500k - 4.99M", calculate(file[Total Revenue], filter(file,and(file[Total Revenue]>=500000,file[Total Revenue]<5000000))),
"4. 5M - 49.9M", calculate(file[Total Revenue], filter(file,and(file[Total Revenue]>=5000000,file[Total Revenue]<500000000))),
"5. 50M - 499M", calculate(file[Total Revenue], filter(file,and(file[Total Revenue]>=50000000,file[Total Revenue]<500000000))),
"6. 500M - 1.99B", calculate(file[Total Revenue], filter(file,and(file[Total Revenue]>=500000000,file[Total Revenue]<2000000000))),
"7. > 2B", calculate(file[Total Revenue], filter(file,file[Total Revenue]>=500000000))
, file[Total Revenue]
)
)
And then I create new table as a relation table, to call above measure as follow.
Range
| 1. 1 - 199k |
| 2. 200k - 499k |
| 3. 500k - 4.99M |
| 4. 5M - 49.9M |
| 5. 50M - 499M |
| 6. 500M - 1.99B |
| 7. > 2B |
The problem is that the scripts run pretty slow, and sometimes I faced 'out of memory' error like this:
Resource Governing: This query uses more memory than the configured limit. The query — or calculations referenced by it — might be too memory-intensive to run
Is there any alternative way that I can do to make it more efficient?
Thank you for your help!
note: I'm using excel table with more than 10 million rows.
