cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jesuisbenjamin Regular Visitor
Regular Visitor

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

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:

 

CodeIndex
201
451

 

 

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:

 

CustomerYearAcquisitionLoss
20201553
20201661
40201440
40201541

 

 

And a calendar table:

 

YearInitial ValueAcquisitionLoss
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.

9 REPLIES 9
lalthan Regular Visitor
Regular Visitor

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

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

jesuisbenjamin Regular Visitor
Regular Visitor

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

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

 

NameAmount
Joe5
Mary4
Tim6
Joe4

 

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:

 

IndexAmount
15
26
37
44
53

 

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

Moderator v-caliao-msft
Moderator

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

Hi @jesuisbenjamin,

 

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

 

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)

 Capture3.PNGCapture4.PNG

 

Regards,

Charlie Liao

Super User
Super User

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


@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))

 

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
jesuisbenjamin Regular Visitor
Regular Visitor

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

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.

Super User
Super User

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


@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 

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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

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.

Super User
Super User

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

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

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Highlighted

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

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