Optimisation of DAX

Stuart · August 15, 2020, 8:15am

Hi All, hoping someone could try and optimise the below for me? There is a lot of repeated words, so hoping by cleaning it p, it will speed up my table:
Current week =
VAR CurrentSummary = SELECTEDVALUE( ‘DRR template’[Summary] )
VAR CurrentLevel1 = SELECTEDVALUE( ‘DRR template’[Level 1] )
VAR CurrentLevel2 = SELECTEDVALUE( ‘DRR template’[Level 2] )
VAR CurrentLevel3 = SELECTEDVALUE( ‘DRR template’[Level 3] )

RETURN
SWITCH( TRUE() ,
CurrentLevel1 = “Occupancy” && CurrentLevel2 = “Transient” && CurrentLevel3 = “Discount”, [Sold rooms Transient Discount],
CurrentLevel1 = “Occupancy” && CurrentLevel2 = “Transient” && CurrentLevel3 = “Negotiated”, [Sold rooms Transient Negotiated],
CurrentLevel1 = “Occupancy” && CurrentLevel2 = “Transient” && CurrentLevel3 = “Qualified”, [Sold rooms Transient Qualified],
CurrentLevel1 = “Occupancy” && CurrentLevel2 = “Transient” && CurrentLevel3 = “Retail”, [Sold rooms Transient Retail],
CurrentLevel1 = “Occupancy” && CurrentLevel2 = “Transient” && CurrentLevel3 = “Wholesale”, [Sold rooms Transient Wholesale],
CurrentLevel1 = “Occupancy” && CurrentLevel2 = “Transient” , [Sold rooms Transient],

CurrentLevel1 = "Occupancy" && CurrentLevel2 = "Group" && CurrentLevel3 = "Association/Convention", [Sold rooms Group Association],
CurrentLevel1 = "Occupancy" && CurrentLevel2 = "Group" && CurrentLevel3 = "Corporate", [Sold rooms Group Corporate],
CurrentLevel1 = "Occupancy" && CurrentLevel2 = "Group" && CurrentLevel3 = "Negotiated", [Sold rooms Group Corporate],
CurrentLevel1 = "Occupancy" && CurrentLevel2 = "Group" && CurrentLevel3 = "Government", [Sold rooms Group Government],
CurrentLevel1 = "Occupancy" && CurrentLevel2 = "Group" && CurrentLevel3 = "SMERF", [Sold rooms Group SMERF],
CurrentLevel1 = "Occupancy" &&CurrentLevel2 = "Group" && CurrentLevel3 = "Tour/Wholesalers", [Sold rooms Group Tour/wholesalers],
CurrentLevel1 = "Occupancy" && CurrentLevel2 = "Group" , [Sold rooms Group],

CurrentLevel1 = "Occupancy" && CurrentLevel2 = "Contract Rooms Revenue" , [Sold rooms Contract],    

CurrentLevel1 = "Occupancy", FORMAT([Occupancy], "0.0%") ,

CurrentLevel1 = "ADR" && CurrentLevel2 = "Transient" && CurrentLevel3 = "Discount", [ADR Transient Discount],
CurrentLevel1 = "ADR" && CurrentLevel2 = "Transient" && CurrentLevel3 = "Negotiated", [ADR Transient Negotiated],
CurrentLevel1 = "ADR" && CurrentLevel2 = "Transient" && CurrentLevel3 = "Qualified", [ADR Transient Qualified],
CurrentLevel1 = "ADR" && CurrentLevel2 = "Transient" && CurrentLevel3 = "Retail", [ADR Transient Retail],
CurrentLevel1 = "ADR" && CurrentLevel2 = "Transient" && CurrentLevel3 = "Wholesale", [ADR Transient Wholesale],
CurrentLevel1 = "ADR" && CurrentLevel2 = "Transient" , [ADR Transient],

CurrentLevel1 = "ADR" && CurrentLevel2 = "Group" && CurrentLevel3 = "Association/Convention", [ADR Group Assocation],
CurrentLevel1 = "ADR" && CurrentLevel2 = "Group" && CurrentLevel3 = "Corproate", [ADR Group Corporate],
CurrentLevel1 = "ADR" && CurrentLevel2 = "Group" && CurrentLevel3 = "Negotiated", [ADR Group Corporate],
CurrentLevel1 = "ADR" && CurrentLevel2 = "Group" && CurrentLevel3 = "Government", [ADR Group Government],
CurrentLevel1 = "ADR" && CurrentLevel2 = "Group" && CurrentLevel3 = "SMERF", [ADR Group SMERF],
CurrentLevel1 = "ADR" && CurrentLevel2 = "Group" && CurrentLevel3 = "Tour/Wholesalers", [ADR Group Tour/Wholesalers],
CurrentLevel1 = "ADR" && CurrentLevel2 = "Group" , [ADR Group],

CurrentLevel1 = "ADR" && CurrentLevel1 = "Contract Rooms Revenue" , [ADR contract],

CurrentLevel1 = "ADR", [ADR],

CurrentLevel1 = "RevPAR", [RevPAR],

CurrentLevel1 = "Rooms Revenue" && CurrentLevel2 = "Transient" && CurrentLevel3 = "Discount", [Rooms Revenue Transient Discount],
CurrentLevel1 = "Rooms Revenue" && CurrentLevel2 = "Transient" && CurrentLevel3 = "Negotiated", [Rooms Revenue Transient Negotiated],
CurrentLevel1 = "Rooms Revenue" && CurrentLevel2 = "Transient" && CurrentLevel3 = "Qualified", [Rooms Revenue Transient Qualified],
CurrentLevel1 = "Rooms Revenue" && CurrentLevel2 = "Transient" && CurrentLevel3 = "Retail", [Rooms Revenue Transient Retail],
CurrentLevel1 = "Rooms Revenue" && CurrentLevel2 = "Transient" && CurrentLevel3 = "Wholesale", [Rooms Revenue Transient Wholesale],
CurrentLevel1 = "Rooms Revenue" && CurrentLevel2 = "Transient" , [Rooms Revenue Transient],

CurrentLevel1 = "Rooms Revenue" && CurrentLevel2 = "Group" && CurrentLevel3 = "Association/Convention", [Rooms Revenue Group Association],
CurrentLevel1 = "Rooms Revenue" && CurrentLevel2 = "Group" && CurrentLevel3 = "Corproate", [Rooms Revenue Group Corporate],
CurrentLevel1 = "Rooms Revenue" && CurrentLevel2 = "Group" && CurrentLevel3 = "Negotiated", [Rooms Revenue Group Corporate],
CurrentLevel1 = "Rooms Revenue" && CurrentLevel2 = "Group" && CurrentLevel3 = "Government", [Rooms Revenue Group Government],
CurrentLevel1 = "Rooms Revenue" && CurrentLevel2 = "Group" && CurrentLevel3 = "SMERF", [Rooms Revenue Group SMERF],
CurrentLevel1 = "Rooms Revenue" && CurrentLevel2 = "Group" && CurrentLevel3 = "Tour/Wholesalers", [Rooms Revenue Group Tour/Wholesalers],
CurrentLevel1 = "Rooms Revenue" && CurrentLevel2 = "Group" , [Rooms Revenue Group],

CurrentLevel1 = "Rooms Revenue" && CurrentLevel2 = "Contract Rooms Revenue" , [Rooms Revenue Contract],

CurrentSummary = "Rooms Revenue", [Rooms Revenue total],

CurrentLevel1 = "Food & Beverage Revenue" && CurrentLevel2 = "Food Revenue" && CurrentLevel3 = "Outlet 1", [F&B Revenue Food Outlet 1],
CurrentLevel1 = "Food & Beverage Revenue" && CurrentLevel2 = "Food Revenue" && CurrentLevel3 = "Outlet 2", [F&B Revenue Food Outlet 2],
CurrentLevel1 = "Food & Beverage Revenue" && CurrentLevel2 = "Food Revenue" && CurrentLevel3 = "Outlet 3", [F&B Revenue Food Outlet 3],
CurrentLevel1 = "Food & Beverage Revenue" && CurrentLevel2 = "Food Revenue" && CurrentLevel3 = "Outlet 4", [F&B Revenue Food Outlet 4],
CurrentLevel1 = "Food & Beverage Revenue" && CurrentLevel2 = "Food Revenue" && CurrentLevel3 = "Outlet 5", [F&B Revenue Food Outlet 5],
CurrentLevel1 = "Food & Beverage Revenue" && CurrentLevel2 = "Food Revenue" && CurrentLevel3 = "Outlet 6", [F&B Revenue Food Outlet 6],
CurrentLevel1 = "Food & Beverage Revenue" && CurrentLevel2 = "Food Revenue" && CurrentLevel3 = "Outlet 7", [F&B Revenue Food Outlet 7],
CurrentLevel1 = "Food & Beverage Revenue" && CurrentLevel2 = "Food Revenue" && CurrentLevel3 = "Outlet 8", [F&B Revenue Food Outlet 8],
CurrentLevel1 = "Food & Beverage Revenue" && CurrentLevel2 = "Food Revenue" && CurrentLevel3 = "Outlet 9", [F&B Revenue Food Outlet 9],
CurrentLevel1 = "Food & Beverage Revenue" && CurrentLevel2 = "Food Revenue", [F&B Revenue Food total],

CurrentLevel1 = "Food & Beverage Revenue" && CurrentLevel2 = "Beverage Revenue" && CurrentLevel3 = "Outlet 1", [F&B Revenue Beverage Outlet 1],
CurrentLevel1 = "Food & Beverage Revenue" && CurrentLevel2 = "Beverage Revenue" && CurrentLevel3 = "Outlet 2", [F&B Revenue Beverage Outlet 2],
CurrentLevel1 = "Food & Beverage Revenue" && CurrentLevel2 = "Beverage Revenue" && CurrentLevel3 = "Outlet 3", [F&B Revenue Beverage Outlet 3],
CurrentLevel1 = "Food & Beverage Revenue" && CurrentLevel2 = "Beverage Revenue" && CurrentLevel3 = "Outlet 4", [F&B Revenue Beverage Outlet 4],
CurrentLevel1 = "Food & Beverage Revenue" && CurrentLevel2 = "Beverage Revenue" && CurrentLevel3 = "Outlet 5", [F&B Revenue Beverage Outlet 5],
CurrentLevel1 = "Food & Beverage Revenue" && CurrentLevel2 = "Beverage Revenue" && CurrentLevel3 = "Outlet 6", [F&B Revenue Beverage Outlet 6],
CurrentLevel1 = "Food & Beverage Revenue" && CurrentLevel2 = "Beverage Revenue" && CurrentLevel3 = "Outlet 7", [F&B Revenue Beverage Outlet 7],
CurrentLevel1 = "Food & Beverage Revenue" && CurrentLevel2 = "Beverage Revenue" && CurrentLevel3 = "Outlet 8", [F&B Revenue Beverage Outlet 8],
CurrentLevel1 = "Food & Beverage Revenue" && CurrentLevel2 = "Beverage Revenue" && CurrentLevel3 = "Outlet 9", [F&B Revenue Beverage Outlet 9],
CurrentLevel1 = "Food & Beverage Revenue" && CurrentLevel2 = "Beverage Revenue", [F&B Revenue Beverage total],

CurrentLevel1 = "Food & Beverage Revenue" && CurrentLevel2 = "Other F&B Revenue" && CurrentLevel3 = "Outlet 1", [F&B Revenue Other Outlet 1],
CurrentLevel1 = "Food & Beverage Revenue" && CurrentLevel2 = "Other F&B Revenue" && CurrentLevel3 = "Outlet 2", [F&B Revenue Other Outlet 2],
CurrentLevel1 = "Food & Beverage Revenue" && CurrentLevel2 = "Other F&B Revenue" && CurrentLevel3 = "Outlet 3", [F&B Revenue Other Outlet 3],
CurrentLevel1 = "Food & Beverage Revenue" && CurrentLevel2 = "Other F&B Revenue" && CurrentLevel3 = "Outlet 4", [F&B Revenue Other Outlet 4],
CurrentLevel1 = "Food & Beverage Revenue" && CurrentLevel2 = "Other F&B Revenue" && CurrentLevel3 = "Outlet 5", [F&B Revenue Other Outlet 5],
CurrentLevel1 = "Food & Beverage Revenue" && CurrentLevel2 = "Other F&B Revenue" && CurrentLevel3 = "Outlet 6", [F&B Revenue Other Outlet 6],
CurrentLevel1 = "Food & Beverage Revenue" && CurrentLevel2 = "Other F&B Revenue" && CurrentLevel3 = "Outlet 7", [F&B Revenue Other Outlet 7],
CurrentLevel1 = "Food & Beverage Revenue" && CurrentLevel2 = "Other F&B Revenue" && CurrentLevel3 = "Outlet 8", [F&B Revenue Other Outlet 8],
CurrentLevel1 = "Food & Beverage Revenue" && CurrentLevel2 = "Other F&B Revenue" && CurrentLevel3 = "Outlet 9", [F&B Revenue Other Outlet 9],
CurrentLevel1 = "Food & Beverage Revenue" && CurrentLevel2 = "Other F&B Revenue", [F&B Revenue Other total],

CurrentLevel1 = "Covers" && CurrentLevel2 = "Outlet 1", [F&B Covers Outlet 1],
CurrentLevel1 = "Covers" && CurrentLevel2 = "Outlet 2", [F&B Covers Outlet 2],
CurrentLevel1 = "Covers" && CurrentLevel2 = "Outlet 3", [F&B Covers Outlet 3],
CurrentLevel1 = "Covers" && CurrentLevel2 = "Outlet 4", [F&B Covers Outlet 4],
CurrentLevel1 = "Covers" && CurrentLevel2 = "Outlet 5", [F&B Covers Outlet 5],
CurrentLevel1 = "Covers" && CurrentLevel2 = "Outlet 6", [F&B Covers Outlet 6],
CurrentLevel1 = "Covers" && CurrentLevel2 = "Outlet 7", [F&B Covers Outlet 7],
CurrentLevel1 = "Covers" && CurrentLevel2 = "Outlet 8", [F&B Covers Outlet 8],
CurrentLevel1 = "Covers" && CurrentLevel2 = "Outlet 9", [F&B Covers Outlet 9],
CurrentLevel1 = "Covers" , [F&B Covers Total],

CurrentLevel1 = "Avg. Check" && CurrentLevel2 = "Avg. Food Check" && CurrentLevel3 = "Outlet 1", [F&B avg Food Check Outlet 1],
CurrentLevel1 = "Avg. Check" && CurrentLevel2 = "Avg. Food Check" && CurrentLevel3 = "Outlet 2", [F&B avg Food Check Outlet 2],
CurrentLevel1 = "Avg. Check" && CurrentLevel2 = "Avg. Food Check" && CurrentLevel3 = "Outlet 3", [F&B avg Food Check Outlet 3],
CurrentLevel1 = "Avg. Check" && CurrentLevel2 = "Avg. Food Check" && CurrentLevel3 = "Outlet 4", [F&B avg Food Check Outlet 4],
CurrentLevel1 = "Avg. Check" && CurrentLevel2 = "Avg. Food Check" && CurrentLevel3 = "Outlet 5", [F&B avg Food Check Outlet 5],
CurrentLevel1 = "Avg. Check" && CurrentLevel2 = "Avg. Food Check" && CurrentLevel3 = "Outlet 6", [F&B avg Food Check Outlet 6],
CurrentLevel1 = "Avg. Check" && CurrentLevel2 = "Avg. Food Check" && CurrentLevel3 = "Outlet 7", [F&B avg Food Check Outlet 7],
CurrentLevel1 = "Avg. Check" && CurrentLevel2 = "Avg. Food Check" && CurrentLevel3 = "Outlet 8", [F&B avg Food Check Outlet 8],
CurrentLevel1 = "Avg. Check" && CurrentLevel2 = "Avg. Food Check" && CurrentLevel3 = "Outlet 9", [F&B avg Food Check Outlet 9],
CurrentLevel1 = "Avg. Check" && CurrentLevel2 = "Avg. Food Check" , [F&B avg Food Check total],

CurrentLevel1 = "Avg. Check" && CurrentLevel2 = "Avg. F&B Check" && CurrentLevel3 = "Outlet 1", [F&B avg Check Outlet 1],
CurrentLevel1 = "Avg. Check" && CurrentLevel2 = "Avg. F&B Check" && CurrentLevel3 = "Outlet 2", [F&B avg Check Outlet 2],
CurrentLevel1 = "Avg. Check" && CurrentLevel2 = "Avg. F&B Check" && CurrentLevel3 = "Outlet 3", [F&B avg Check Outlet 3],
CurrentLevel1 = "Avg. Check" && CurrentLevel2 = "Avg. F&B Check" && CurrentLevel3 = "Outlet 4", [F&B avg Check Outlet 4],
CurrentLevel1 = "Avg. Check" && CurrentLevel2 = "Avg. F&B Check" && CurrentLevel3 = "Outlet 5", [F&B avg Check Outlet 5],
CurrentLevel1 = "Avg. Check" && CurrentLevel2 = "Avg. F&B Check" && CurrentLevel3 = "Outlet 6", [F&B avg Check Outlet 6],
CurrentLevel1 = "Avg. Check" && CurrentLevel2 = "Avg. F&B Check" && CurrentLevel3 = "Outlet 7", [F&B avg Check Outlet 7],
CurrentLevel1 = "Avg. Check" && CurrentLevel2 = "Avg. F&B Check" && CurrentLevel3 = "Outlet 8", [F&B avg Check Outlet 8],
CurrentLevel1 = "Avg. Check" && CurrentLevel2 = "Avg. F&B Check" && CurrentLevel3 = "Outlet 9", [F&B avg Check Outlet 9],
CurrentLevel1 = "Avg. Check" && CurrentLevel2 = "Avg. F&B Check" , [F&B avg Check total],
CurrentLevel1 = "Avg. Check" , [F&B avg Check total],

CurrentSummary = "Food & Beverage Revenue" , [F&B Revenue Beverage total] + [F&B Revenue Food total] + [F&B Revenue Other total],

      BLANK() )

AntrikshSharma · August 15, 2020, 8:36am

I don’t see a lot of cleanup areas but you can start with something like this:

Stuart · August 15, 2020, 2:48pm

Thanks. Tried this with little success. My file is only 720kb, with no huge formulae, but it is taking over 20 seconds to make any change…

AntrikshSharma · August 15, 2020, 3:02pm

DAX editor in PBI is not really great, and you have around 200 + rows of code so it will be slow.

Stuart · August 15, 2020, 3:04pm

So no way to speed it up?

AntrikshSharma · August 15, 2020, 3:13pm

Nope, but why are you using so many measure with so many conditions, where do you use this measure? If there is a data modelling issue you should solve it with PQ.

BrianJ · August 15, 2020, 3:20pm

@Stuart,

Totally agree with @AntrikshSharma. Seems to me this should all be done in PQ, which makes both your performance issues and unwieldy DAX problem disappear.

Brian

Stuart · August 15, 2020, 3:21pm

I am trying to “layer” the data into a matrix table. Is there an easier way to do this?

Stuart · August 16, 2020, 8:51am

Hi @BrianJ, what is PQ?

Melissa · August 16, 2020, 9:29am

Hi @Stuart,

I’m sure @BrianJ won’t mind me answering this one
PQ is shorthand for Power Query.

Stuart · August 16, 2020, 12:52pm

Thanks @Melissa. Where can I find training on how to set this up (the training on PQ in Enterprise DNA doesn’t appear to focus on my issue)

Harsh · August 16, 2020, 2:33pm

Hello @Stuart,

Here are the links provided for training on PQ.

Thanks & Warm Regards,
Harsh

EnterpriseDNA · August 17, 2020, 12:38am

Hi @Stuart, did the response provided by the contributors help you solve your query? If not, how far did you get, and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

EnterpriseDNA · August 18, 2020, 1:31am

Hi @Stuart, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!