cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## 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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User

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

@_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```

Proud to be a Datanaut! Appreciate your Kudos
Feel free to email me with any of your BI needs.

Super User

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

@_fmigg here is the output

Proud to be a Datanaut! Appreciate your Kudos
Feel free to email me with any of your BI needs.

3 REPLIES 3
Super User

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

@_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```

Proud to be a Datanaut! Appreciate your Kudos
Feel free to email me with any of your BI needs.

Super User

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

@_fmigg here is the output

Proud to be a Datanaut! Appreciate your Kudos
Feel free to email me with any of your BI needs.

Super User

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

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

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 181 members 2,157 guests
Recent signins: