cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
_fmigg Frequent Visitor
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
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





Did I answer your question? Mark my post as a solution.

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





Super User
Super User

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

@_fmigg here is the output

 

image.png






Did I answer your question? Mark my post as a solution.

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





3 REPLIES 3
Super User
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





Did I answer your question? Mark my post as a solution.

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





Super User
Super User

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

@_fmigg here is the output

 

image.png






Did I answer your question? Mark my post as a solution.

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





Super User
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  Datanaut

 

Helpful resources

Announcements
Virtual Launch Event

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

Power BI Helps Homeless and Trouble Youth

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 181 members 2,157 guests
Please welcome our newest community members: