dax debugger
1 Topic
Help with optimizing DAX measure - Income statement
Hi community! I was hoping that someone out there can help me with some DAX or at least put me in the right direction. I will spend a bit time time to explain my set up and what i am trying to achieve. Essentially i am trying to build an income statement in Power BI. In my semantic model i have G L Accounts that i am using to create a sharepoint lists, linking the accounts to subcategories and categories (this is actually also in my model, however i needed a custom mapping so thats why i have it in SharePoint). However, there are some categories that i dont have in my model such as Gross Profit, Overheads, EBITDA or EBIT, that i have tried to construct in my DAX measure as you can see below. Those names i have created in the SharePoint List so that the model knows them without them having any data linked to them. The code example i provided is for Last year same period, but i have similar constructed measures just for other scenarios like YTD and FY. I have in my report a matrix table that contains the three levels; category, subcategory and account item and the categories are returned as expected with the correct values, but the issue i am running into is that when i expand to level 2 the performance is significalty slower and on level 3 it becomes even worse. So my 1 million dollar question is if there are a way i can optimize the code? Bonus info: the performance is in fact not a big issue on all three level, however i have noticed whenever i have enabled data bars which i use for variance columns the performance becomes a big issue for me when expanding the rows. Thank you! P&L LY = VAR _Category = SELECTEDVALUE('G L Account Category SharePoint'[G L Account Category]) RETURN SWITCH( TRUE(), _Category = "Gross Profit", CALCULATE( [ACT LY], REMOVEFILTERS('G L Account Category SharePoint'), 'G L Account Category SharePoint'[Index] < 3 ), _Category = "Total Overheads", CALCULATE( [ACT LY], REMOVEFILTERS('G L Account Category SharePoint'), 'G L Account Category SharePoint'[Index] < 7 && 'G L Account Category SharePoint'[Index] > 3 ), _Category = "Total Personnel Costs", CALCULATE( [ACT LY], REMOVEFILTERS('G L Account Category SharePoint'), 'G L Account Category SharePoint'[Index] < 10 && 'G L Account Category SharePoint'[Index] > 7 ), _Category = "EBITDA", CALCULATE( [ACT LY], REMOVEFILTERS('G L Account Category SharePoint'), 'G L Account Category SharePoint'[Index] < 13 ), _Category = "EBIT", CALCULATE( [ACT LY], REMOVEFILTERS('G L Account Category SharePoint'), 'G L Account Category SharePoint'[Index] < 15 ), [ACT LY] )Solved466Views0likes2Comments