Extract a Single Value from a Column with Duplicates, Filtered by Another Column

[Anonymous]

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!

[Greg_Deckler]

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

    )

)

[Anonymous]

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 · Accepted 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

[Anonymous]

Thank you very much! I appreciate the help.