Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

DAX Newbie Question - Base Table Expected

I know that I can define variables & reuse them in subsequent expressions to create more readable code.

I've seen others (better) do it

Keep coming across '... base table is expected'

For example -

 

// Using Wide World Importers.Application.StateProvinces 
DEFINE 

 

//Sum population of States in a Sales Territory -> Works OK
VAR _Table1 = SUMMARIZE(States,States[Territory],"Population",SUM(States[Population]))

//Find most populated Sales Territory -> Works OK
VAR MAXPOP = MAXX(_Table1,[Population])

//Put them both together = base table is expected
VAR Result1 = CALCULATETABLE(_Table1[Population],FILTER(_Table1,[Population] = MAXPOP))

//So, lets do this -> Works OK
VAR Result2 = CALCULATETABLE(SUMMARIZE(States,States[Territory],"Population",SUM(States[Population])),FILTER(_Table1,[Population] = MAXPOP))

EVALUATE Result2

 

DAX is quite happy

 

 

Where I would like to end up is a scalar variable = "Southeast"

 

DAX is happy with - (for persistent table)

 

DEFINE
VAR DenseState = MAX(States[Population])
VAR getState = LOOKUPVALUE(States[State Name],[Population],DenseState)
EVALUATE {getState}

 

 But not happy with - (virtual / cte / #temp kind of table)

 

VAR getTerritory = LOOKUPVALUE(_Table1[Territory],[Population],[MAXPOP])

 

2 Replies

  • MattAllington's avatar
    MattAllington
    Icon for Community Champion rankCommunity Champion

    It looks to me you are approaching DAX as if it were a query tool replacement for SQL. This is not its primary purpose and in fact this is quite a niche approach. Power BI is a model based tool. You build a model which includes tables, relationships and measures, and then visualise the data using visuals in a report. You can do all of this and never write a single dax query - the queries are managed by the visuals. 

    I have taught thousands of people DAX, and i see this all the time with people coming from a rdbms background. Take a step back and explain what are you trying to do. Chances are there is a better way. 

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi Matt, thank you for the response. I guess I didn't explain myself very well. It is the "... base table expected" that was throwing me for a while. Found this Implementing Input Table in DAX Query – Prologika and has put me on the right track. Cross-posting here just in case others may end up in the same place as I did.

       

      New (ish) to DAX, but no stranger to SQL, C#, Java and way back when Turbo Pascal. Also no stranger to Kimball (ish) EDW, althougth Inmon was the fashion when I first started. "Clicky-clicky, draggy-droppy", as the saying goes, is all very well, just not always what you want.

       

      Many thanks