Forum Discussion

Kaycee's avatar
Kaycee
Advocate I
2 years ago

Cannot Reference Field in Table for Filter in CALCULATE

Hi all, I've written the below DAX to calculate a list of all KPIs showing only the 3rd best branch's result. I've provided the full code for reference however, I've pulled the troublesome part of...
Need Help
MattAllington's avatar
MattAllington
Community Champion
2 years ago

I've been teaching DAX for 10 years now. I realised very early on that there are two types of people that learn DAX: there are people that come from an Excel background and people that come from a SQL server background. You are clearly the latter.

 

Each of these two groups make different mistakes. Excel users always try to solve problems with calculated columns. SQL users always try to solve problems by writing queries.  Both of these approaches are wrong. 

 

you need to use this tool differently to SQL server. You need to stop writing the query using DAX like a scripting language and start to use it as a UI driven, model based tool. 

  1. go to a blank report page, add a matrix (or table) to the canvas and start to build the structure of the output you want to see. 
  2. add columns from your model into the visual
  3. write simply measures that help you on your journey to solve the problem. Add them to the visual so you can see the results.
  4. continue to write measures referencing the earlier measures until the problem is solved

above all, do not do anything that involves the EVALUATE statement. 

Kaycee's avatar
Kaycee
Advocate I
2 years ago

Hi MattAllington , 

Thanks for taking the time to reply.  I'm afraid you are incorrect - I was an accountant by trade but after a decade of intensive learning in BI, I switch between a range of options for solving challenges that best suit the situation.  I'm guessing my approach in this instance is a reflection of the many people I've learnt from over the years.

With respect to your approach, I can see your point however I'd appreciate your insight on two aspects of your recommendation:
1)  How do you then efficiently transition your solution back to a calculated table after using the UI interface when you consider a measure to not be the optimal outcome? 

2) What about the learning aspect?  I can find another way to write this that would avoid the error - however I chose to put it in the forum anyway, as the result isn't as I would expect and I've been unable to resolve why the error is happening.  The understanding is of greater importance to me than the resolution of the code itself. 

Thanks again
Kaycee