Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
@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))
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.
@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
@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:
Hi Matt, I find the jargon in your answer a bit too abstruse so I want to buy your book but,
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.
I hope the security on my site/shop is now fixed. Thnaks for alerting me to it.
Cheers Matt, I confirm that the security is now fixed.
Most probably, you have not marked your calendar table as a "Date Table"
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |