Confusion as to what columns I can use for measures and filters

[jesuisbenjamin]

I'm new to Power BI and DAX and I find something really confusing:

I cannot understand why I cannot use some columns in measures and filters.

Example:

If I have a table with:

Code	Index
20	1
45	1

I can do measure = SUM([Code],[Index]) but if I use CONCATENATE() instead of SUM() it cannot find the [Code] and [Index] columns. Yet if I run the SUM([Code],[Index] as Measure 1 and then try to CONCATENATE() as Measure 2 it can find the Measure 1 column?

Next, I have a record table:

Customer	Year	Acquisition	Loss
20	2015	5	3
20	2016	6	1
40	2014	4	0
40	2015	4	1

And a calendar table:

Year	Initial Value	Acquisition	Loss
2014
2015
2016

To calculate initial value per year I need to calculate the difference bewtween acquisitions and loss of the prior year. I attempt the following:

Initial Value = CALCULATE(record[acquisition]-record[loss], FILTER(ALL(record[Year]), record[Year]=[Year]-1))

However it cannot find the [Year] of my calendar table, even if I explicitely write calendar[Year]-1.

What's the problem with that? I'm really confused here.

[MattAllington]

---

@jesuisbenjamin wrote:

I cannot understand why I cannot use some columns in measures and filters.

... 

What's the problem with that? I'm really confused here.

---

Power BI has a powerful Intellisense engine. It will not allow you to write invalid DAX. The formulas you describe are not valid - that is why you can't write them. 

This is valid

=calculate([measure], filter(all(table), table[column] = max(table[column])))

the following is not valid (which is why you can't write it)

=calculate([measure], filter(all(table), table[column] = table[column] -1))

[jesuisbenjamin]

Hi Matt,

Thanks for this.

What's the role of MAX() here? It seems that you are passing the value of the cell with this.

[MattAllington]

---

@jesuisbenjamin wrote:

What's the role of MAX() here? It seems that you are passing the value of the cell with this.

---

You ask a very good question. I can't do the explanation justice here because there are layers of knowledge you need before you can truly grasp everything. Also some of these terms will probably be foreign to you.  But here goes

Filter is an iterator and hence it creates its own row context (just like a table in Excel). Whenever there is a row context, there is not automatically a filter context.

MAX() is an aggregator function and will always operate across whatever filter context exists. Because Filter creates a row context but not a filter context, MAX() has the effect of "reading" the filter context from the visualisation from where the outer formula operates. You can imagine the max() (or any other aggregator for that matter) reaching outside of the filter function to "grab" the current value from the visual. 

Conversley, table[column] used inside filter is what I call a "naked colum". Naked in that it isn't wrapped inside an aggregator. 

So the way you read the filter function is whenever you see a naked column, you should think about the underlying table itself, and whenever you see an aggregator, you should think about the value coming from the visualisation (the max value of table[column] in the current filter context in this case).  So the filter formula says "go and put a new filter on table[column] that is equal to the maximum value of that same column in the current visualisation. 

I cover this in detail in my book including all the foundational knowledge you will need to deeply understand what is going on. Could be US$15 well spent for a digital copy. Http://xbi.com.au/learndax 

[Applicable88]

@MattAllington that is one of the best explanation I read so far. I know its an old thread, but I recently posted a similar question, since I find exceptions to the rule. You wrote:

---

"So the way you read the filter function is whenever you see a naked column, you should think about the underlying table itself, and whenever you see an aggregator, you should think about the value coming from the visualisation (the max value of table[column] in the current filter context in this case).  So the filter formula says "go and put a new filter on table[column] that is equal to the maximum value of that same column in the current visualisation." 

And although that points are totally correct, I still don't understand exceptions like these examples:

Measure 1 =

calculate([TotalSales]),

Filter(

ALL('Calendar'[Date]),

'Calendar'[Date]<= MAX('Calendar'[Date])

))

in this Measure  above I need to wrap up Calendar[Date] within a aggregation like MAX and somehow it finds the filter context to calendar date. It is actually inside the filter of a interator. But if I just write [CalendarDate]<= [CalendarDate] I get only the total sum in every row. Since Filter() returns a table, shouldn't it work without aggregation?

Note that in the next example I'm not calculating the same here, but writing the same kind of dax syntax within Filter() with a naked colum for a calculated column, which by itself is already a row by row iterator and  I do get the right values:

Column= 

Var _TableVariable= Filter( 'Calendar', 'Calendar' [Date]>'Maintable'[BookingDate] && 'Calendar[Day]= "Monday"))
return
countrows(_TableVariable) 

Here I don't need any aggregator I can just write naked column and when computing every row, it knows which date and values to compute. But in the table variable of the Measure, it's also in a row to row context via Filter-Function, why I cannot use naked columns there? You said the Max aggregation has the "effect" of reading the filtercontext, but aren't we there in a table as well? 

We also see naked columns everyday, when we do calculation with x-aggregated iterators. One more argument why its strange that it cannot be done in a Filter() function. 

I hope you can adress these points. 

Thank you very much.

Best. 

[RobertSlattery]

Hi Matt, I find the jargon in your answer a bit too abstruse so I want to buy your book but,

1. The link here is broken
2. When I visit your site, the checkout doesn't have a lock icon in the nav bar so I'm not comfortable entering private information

Is there a secure way for me to purchase the e-book?

I do get a bit of an idea that the context in an if statement for example, is like the context in a Table.Group statement in M so I can understand that Sum will return the row value of a field (assuming it is numeric).  The added layer of confusion is what's going on in the Totals row.  I assume (based on observation) that the filter context there is the entire table.  This means I can do the equivalent of a SumIf at a row level but, I can't summarise it.

[MattAllington]

I hope the security on my site/shop is now fixed. Thnaks for alerting me to it. 

[RobertSlattery]

Cheers Matt, I confirm that the security is now fixed.

[lalthan]

Most probably, you have not marked your calendar table as a "Date Table"

[jesuisbenjamin]

I don't think it's got to do with dates, I can create the same problem with another non-date table:

Name	Amount
Joe	5
Mary	4
Tim	6
Joe	4

If want to create a measure called Total Amout that would sum all purchases per person, I cannot:

Total Amount Measure = CALCULATE(SUM(table[amount]),FILTER(ALL(table[name], table[name]=value(table[name])))) won't work

Neither can I lookup the another index on this table:

Index	Amount
1	5
2	6
3	7
4	4
5	3

Previous Amount Measure = LOOKUPVALUE(Table2[Amount],Table2[Index],table2[Index]-1) won't work.

[v-caliao-msft]

Hi @jesuisbenjamin,

To calculated the total sale for each person in measure, you can use the DAX below.
YearInitialValue = SUMMARIZE(Record,Record[Year])

To get the initial value for each year, please add calcualted column in the original table.
Profit = Record[Acquisition]-Record[Loss]
Total = CALCULATE(SUM(Record[Profit]),ALLEXCEPT(Record,Record[Year]))
And then add calculated column in the Year table.
InitialYearValue = LOOKUPVALUE(Record[Total],Record[Year],YearInitialValue[Year]-1)

 

Regards,

Charlie Liao