topic Re: If date equals specific date than create a constant in DAX Commands and Tips

If date equals specific date than create a constant

ngutc — Thu, 21 Jul 2022 09:00:42 GMT

Hello,

I'm working with a live database and i would like to create a measure where, if table(date[year]) equals 2005 then make a constant 5 else leave blank. If function does not take the date table and I'm not sure if I'm doing it right.

I want results like this

2001	3
2002
2003	1
2004
2005	5
2006

But create with DAX.

Can someone help? Thank you

Re: If date equals specific date than create a constant

tamerj1 — Thu, 21 Jul 2022 09:38:28 GMT

Hi ngutc
Please use

= SWITCH ( SELECTEDVALUE ( 'Date'[year] ), 2001, 3, 2003, 1, 2005, 5 )

I noticed maybe you just want to take the right digit of odd years. If this is the case then use

= VAR CurrentYear = SELECTEDVALUE ( 'Date'[year] ) RETURN IF ( ISODD ( CurrentYear ), VALUE ( RIGHT ( CurrentYear, 1 ) ) )

Re: If date equals specific date than create a constant

ngutc — Thu, 21 Jul 2022 09:46:10 GMT

Thank you a lot! It worked with the switch function! I actaully wanted the constants for every 5 years, not for the odd ones.

Re: If date equals specific date than create a constant

tamerj1 — Thu, 21 Jul 2022 09:50:07 GMT

ngutc

Then this one

= VAR CurrentYear = SELECTEDVALUE ( 'Date'[year] ) RETURN IF ( MOD ( CurrentYear, 5 ) = 0, VALUE ( RIGHT ( CurrentYear, 1 ) ) )

Re: If date equals specific date than create a constant

ngutc — Thu, 21 Jul 2022 10:12:50 GMT

what i get is this

2000	0
2001
2002
2003
2004
2005	5
2006
2007
2008
2009
2010	0

I want to be able to declare for each five years a different constant.

Re: If date equals specific date than create a constant

tamerj1 — Thu, 21 Jul 2022 10:15:08 GMT

Hi ngutc
Would yu be more specific? Do you have a list?

Re: If date equals specific date than create a constant

ngutc — Thu, 21 Jul 2022 10:22:59 GMT

It's like target numbers for each five years. For example i want for year target 5 and in year 2010 target 11. Tha values do not have any pattern and it's really random. But I think the switch function does it's purpose! Thank you!

Re: If date equals specific date than create a constant

tamerj1 — Thu, 21 Jul 2022 10:33:53 GMT

I Understand but can you provide me this list where you have the year and the target value. I mean it is easier to import this table locally and create a relationship with your table from the live database. Otherwise this list would be helpful even to create an independent measure.

Re: If date equals specific date than create a constant

ngutc — Thu, 28 Jul 2022 06:35:19 GMT

The list with values are

2000	0
2001
2002
2003
2004
2005	6
2006
2007
2008
2009
2010	10
2011
2012
2013
2014
2015	15
2016
2017
2018
2019
2020	21
2021
2022

There is literally no pattern how the values go and it's really random (only that it's for every five years), so with the switch measure I think it could be done. If in the future there will be more values, I would consider creating a table and their relationship in the live database.

If there would be a measure that could be more indepedent, I'm not sure how.

If you have any ideas, that would be great, but you helped me a lot.

Thank you!

Re: If date equals specific date than create a constant

tamerj1 — Thu, 28 Jul 2022 08:57:00 GMT

Hi ngutc
easiest is to have this list as a table in your model connected with the main table or with the date table. If you can provide more context about your model and what exactly is the ultimate required result, then I can advise how exactly to plug in this table into your model and use use it to generate the required output. Then if this table is updated in anytime the results will be updated automatically