Forum Discussion
Power BI taking ages to apply query changes
Community ChampionThe model you work on will be used with the PowerBI visuals, correct?
The logic you describe should be much easier to achieve in DAX, possibly even without storing cumulative values, but just with cumulative measures. Is that an option for you or is there a particular reason why you need to store the data in the table like that?
Yes, everything will be in Power BI. I don't know DAX very well, though- how would I go about exploring it?
Thanks,
Jason
- Stachu
it's quite a journey before you :smileywink:
this topic has lots of sources:
https://community.powerbi.com/t5/Desktop/Good-Source-to-Learn-DAX/m-p/537748here is an example for the cumulative totals:
https://www.daxpatterns.com/cumulative-total/
regarding your particular model it would be better to revisit once you learn the basic concepts, especially if you come from SQL background - DAX has very different way of solving same problems
if you're under time pressure though here are few tips:
1) instead of quarters as text I'd use dates (e.g. 2019-01-01 for 2019Q1, 2019-04-01 for 2019Q2). This enables using more operators, as well as gives access to the time intelligence functions
2) build a separate Calendar (date dimension) table (for the benefit of time intelligence):
https://www.sqlbi.com/articles/automatic-time-intelligence-in-power-bi/
https://docs.microsoft.com/en-us/power-bi/desktop-date-tables
3) if the baseline scenario is just prior year figures you could calculate these values based on Actual & Calendar table (e.g. using SAMEPERIODLASTYEAR)
https://docs.microsoft.com/en-us/dax/sameperiodlastyear-function-dax