Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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] a...
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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:
RevenueNet RevenueAcademic YearCurrent Year Flag
100802015FALSE
2501002015FALSE
40-202015FALSE
5355202016FALSE
5425002016FALSE
886-702017FALSE
5465222017FALSE
3853802018FALSE
518632002018FALSE
55122018FALSE
691550002019FALSE
486-252019FALSE
54202020TRUE
538-1002020TRUE
4873002020TRUE
9975002020TRUE
573722021FALSE
331-202021FALSE
12-802022FALSE
347502022FALSE
7987002022FALSE

 

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    Thank you very much! I appreciate the help.