Forum Discussion
Extract a Single Value from a Column with Duplicates, Filtered by Another Column
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 CALCULATEI 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
Community ChampionFirst, 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.
The sample data (from the newer schema I'm working off of, with a table name of 'dw B2 Class View') is as follows: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 - Greg_Deckler
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 CALCULATEI 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.