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.
Hi,
I am wrestling with what I think should be a very simple DAX measure problem, but nothing I have tried yet has worked. Here's the situation:
I have a table with colulmns [Academic Year] and [Current Year Flag]. There are duplicate values in both of these columns, since they are part of a table that inludes all classes offered at an institution, and details about those classes. Because multiple classes are offered in a year, there are duplicate year values.
Anyway, my goal is to calculate a measure (we'll call it Metric Previous Year) based on a particular column's values from a previous year, i.e., current academic year - 1. Someone before me (using an earlier data warehouse source with different architecture and different field names) approached it like this:
Metric Previous Year =
CALCULATE (
[Metric],
FILTER (
ALL ( 'Term Dim' ),
'Term Dim'[Academic Year Numeric]
= MAX ( 'Term Dim'[Academic Year Numeric] ) - 1
&& 'Term Dim'[Quarter] IN VALUES ( 'Term Dim'[Quarter] )
)
)
In other words, they used MAX to find the largest value in [Academic Year Numeric] and subtracted 1 to find the previous year. But in the data warehous source I am working with, there are some [Academic Year] values for years in the future, meaning that MAX will return a higher number than the current year, and if I subtract 1 I will not get last year.
What I need to do is extract a single value for [Academic Year] where [Current Year Flag] = True, then use this year to subtract and find the previous year. As a beginner, it seems like I should be able to do this with FILTER() and DISTINCT(), but something confusing about the context or something else I don't understand has prevented me from getting this working. Any suggestions are greatly appreciated!
Thanks!
Solved! Go to Solution.
Looks like I left out a paren or something, try:
Metric Prior Year =
CALCULATE (
[Metric],
FILTER (
ALL ( 'dw B2 Class View' ),
'dw B2 Class View'[Academic Year]
= MAXX
(
FILTER(
'dw B2 Class View',
[Current Year Flag] = 1 &&
'dw B2 Class View'[Academic Year] IN DISTINCT ( 'dw B2 Class View'[Academic Year] )
), //end second FILTER
[Academic Year]
) //end MAXX
- 1
) // end first FILTER
) // end CALCULATE
I also replaced VALUES with DISTINCT. I just wrote a blog article on this topic, but that wasn't because of you. https://community.powerbi.com/t5/Community-Blog/Soapbox-Series-Adding-No-VALUE-S/ba-p/1059818
First, sample data would be tremendously helpful: Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
But, perhaps:
CALCULATE (
[Metric],
FILTER (
ALL ( 'Term Dim' ),
'Term Dim'[Academic Year Numeric]
= MAXX ( FILTER('Term Dim',[Current Year Flag] = 1,[Academic Year Numeric] ) - 1
&& 'Term Dim'[Quarter] IN VALUES ( 'Term Dim'[Quarter] )
)
)
Hi @Greg_Deckler,
You're absolutely right - I should have provided sample data. It would have made it easier for people to answer my question, and it would have helped me catch an important omission I didn't notice in my original question. The metric in question (which I called [Metric]) is actually a calculated measure:
Metric =
DIVIDE ( SUM ( [Net Revenue] ), SUM ( [Revenue] ) )
Using the updated field and table names (from the newer schema I'm working off of, with a table name of 'dw B2 Class View'), your proposed solution would - I think - look like this:
Metric Prior Year =
CALCULATE (
[Metric],
FILTER (
ALL ( 'dw B2 Class View' ),
'dw B2 Class View'[Academic Year]
= MAXX ( FILTER('dw B2 Class View', [Current Year Flag] = 1,[Academic Year] ) - 1
&& 'dw B2 Class View'[Academic Year] IN VALUES ( 'dw B2 Class View'[Academic Year] )
)
)
)
The MAXX function seems like the right thing to use, but I run into a "too many arguments were passed into the FILTER function" problem. And I think that is actually the root of the problem - trying to figure out how to filter the table based on one column while returning a single related value from a different column.
Thank you again so much for your help, and sorry for not getting my question right the first time.
Revenue | Net Revenue | Academic Year | Current Year Flag |
100 | 80 | 2015 | FALSE |
250 | 100 | 2015 | FALSE |
40 | -20 | 2015 | FALSE |
535 | 520 | 2016 | FALSE |
542 | 500 | 2016 | FALSE |
886 | -70 | 2017 | FALSE |
546 | 522 | 2017 | FALSE |
385 | 380 | 2018 | FALSE |
5186 | 3200 | 2018 | FALSE |
55 | 12 | 2018 | FALSE |
6915 | 5000 | 2019 | FALSE |
486 | -25 | 2019 | FALSE |
54 | 20 | 2020 | TRUE |
538 | -100 | 2020 | TRUE |
487 | 300 | 2020 | TRUE |
997 | 500 | 2020 | TRUE |
573 | 72 | 2021 | FALSE |
331 | -20 | 2021 | FALSE |
12 | -80 | 2022 | FALSE |
347 | 50 | 2022 | FALSE |
798 | 700 | 2022 | FALSE
|
Looks like I left out a paren or something, try:
Metric Prior Year =
CALCULATE (
[Metric],
FILTER (
ALL ( 'dw B2 Class View' ),
'dw B2 Class View'[Academic Year]
= MAXX
(
FILTER(
'dw B2 Class View',
[Current Year Flag] = 1 &&
'dw B2 Class View'[Academic Year] IN DISTINCT ( 'dw B2 Class View'[Academic Year] )
), //end second FILTER
[Academic Year]
) //end MAXX
- 1
) // end first FILTER
) // end CALCULATE
I also replaced VALUES with DISTINCT. I just wrote a blog article on this topic, but that wasn't because of you. https://community.powerbi.com/t5/Community-Blog/Soapbox-Series-Adding-No-VALUE-S/ba-p/1059818
Thank you very much! I appreciate the help.
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 |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
46 | |
31 | |
30 | |
18 | |
18 |