Forum Discussion

_fmigg's avatar
_fmigg
Regular Visitor
6 years ago
Solved

Create a Calculated Column (or one-column table) as VAR using criteria.

Hi, 

I'm trying to create a measure that indicates with a 1 or 0 based on the following data: 

Year	Month
2019	Jan
2019	Feb
2019	Mar
2019	Apr
2019	May
2019	Jun
2019	Jul
2019	Aug
2019	Sep
2019	Oct
2019	Nov
2019	Dec
2020	Jan
2020	Feb
2020	Mar

If the Month exists in the MAX Year, I will return a 1, otherwise 0. 

So the measure should work like this if is added to a visual table: 

Year	Month	isMonthInMaxYear
2019	Jan	1
2019	Feb	1
2019	Mar	1
2019	Apr	0
2019	May	0
2019	Jun	0
2019	Jul	0
2019	Aug	0
2019	Sep	0
2019	Oct	0
2019	Nov	0
2019	Dec	0
2020	Jan	1
2020	Feb	1
2020	Mar	1

I can't solve it statically because at the future there will be more records on the table and the 1's will be changing over time, neither can't create more tables at the data model (nor calculated tables or Power Query tables).

What I'm trying to do is to have a table a one-column table as VAR on the measure, but I'm kind of stuck because VALUES function doesn't accept criteria, SUMMARIZECOLUMNS asks for an index column in which criteria can't be applied and CALCULATE TABLE will return the table with Year and Month columns. 


I'd like to have a table with the months of the highest year in a VAR, so the table would look like this. 

Month
Jan
Feb
Mar

And then use it on a condition using the IN function, but I'm stuck on the previous step.

Is it possible to do that? Is this approach correct? 

Thanks

Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    _fmigg try this DAX

     

    Is month found = 
VAR __maxYear = CALCULATE( MAX ( 'Table'[Year] ), ALL() )
VAR __months = CALCULATETABLE( VALUES( 'Table'[Month] ), 'Table'[Year] = __maxYear)
RETURN
COUNTROWS( __months ) + 0

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    _fmigg try this DAX

     

    Is month found = 
VAR __maxYear = CALCULATE( MAX ( 'Table'[Year] ), ALL() )
VAR __months = CALCULATETABLE( VALUES( 'Table'[Month] ), 'Table'[Year] = __maxYear)
RETURN
COUNTROWS( __months ) + 0
  • AlB's avatar
    AlB
    Community Champion
    6 years ago

    Hi _fmigg 

    If your measure works correctly, you can just get that one-column table with: 

    FILTER( VALUES( Table1[Month] ), [isMonthInMaxYear] = 1)

    and then use it in a VAR or wherever you need it. If this doesn't work do show the code for the measure as it migt need adjusting

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Cheers  Datanaut