How to combine 2 expression into 1 ?

[admin11]

Hi All

I have below 2 seperate expression :-

Show Last 3 Yr = IF ('DATE'[Year] >= Year(TODAY()) - 3, 1, 0)

Show Last 4 Yr = IF ('DATE'[Year] >= Year(TODAY()) - 4, 1, 0)

May i know how to combine both of them into 1 , look some thing like below :-

Show Last 3 or 4 Yr =

IF ('DATE'[Year] >= Year(TODAY()) - 3, 3, 0)

IF ('DATE'[Year] >= Year(TODAY()) - 4, 4, 0)

So that i can on fly change from 3 to 4 year filter

Paul

[VahidDM]

Hi   @admin11 

Try this:

Show Last 3 or 4 Yr =
VAR _Y3 =
    'DATE'[Year]
        >= YEAR ( TODAY () ) - 3
VAR _Y4 =
    'DATE'[Year]
        >= YEAR ( TODAY () ) - 4
RETURN
    IF ( 'DATE'[Year] >= _Y3, 3, IF ( 'DATE'[Year] = _Y4, 4, 0 ) )

it will return 3 for the first 3 years and 4 for a year before that.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[admin11]

Hi @VahidDM 

Thank you for your sharing . i get error :-

DAX comparison operations do not support comparing values of type Integer with values of type True/False. Consider using the VALUE or FORMAT function to convert one of the values.

my PBI sample file :-

https://www.dropbox.com/s/rf7o60s9y0yhxpd/show%203%20or%204%20yr.pbix?dl=0

Hope you can take a look

Paul

[VahidDM]

@admin11 

My bad.

Try this:

Show Last 3 or 4 Yr =
VAR _Y3 =
    YEAR ( TODAY () ) - 3
VAR _Y4 =
    YEAR ( TODAY () ) - 4
RETURN
    IF ( 'DATE'[Year] >= _Y3, 3, IF ( 'DATE'[Year] = _Y4, 4, 0 ) )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[admin11]

@VahidDM 

Great now no more error. 

Not the situation is when i Tick box = 3 :-

it display 2019 , 2020 , 2021 , 2022

If i tick Box = 4 :-

it display 2018 only

it is possible to make it display2018 2019 , 2020 , 2021 , 2022

Hope you can share with me

Paul

it is possible to make it , when i tick box = 4 , 

[AlexisOlson]

You aren't going to be able to do that with a calculated column since each year can only belong to at most one of 3 or 4.

You can approach this in a different way though and create a new parameter table to use for the slicer and then define a measure to use as a filter on your date slicer.

For the option to select 1-5 years back, define a new calculated table like this:

ShowLast = SELECTCOLUMNS ( GENERATESERIES(1, 5), "Year", [Value] )

Then you can define a measure like this which you'll then set as a visual filter for the date table year slicer:

YearFilter = 
VAR SelectedYear = SELECTEDVALUE ( ShowLast[Year], MAX ('Date'[Year] ) )
VAR CalendarYear = MAX ( 'Date'[Year] )
RETURN
    IF ( YEAR ( TODAY() ) - CalendarYear <= SelectedYear, 1, 0 )

See the attached file.

[admin11]

@AlexisOlson 

Thank you for your help , i get below error :-

A table of multiple values was supplied where a single value was expected.

https://www.dropbox.com/s/ax5w2w8ygxzzw8h/PBS_VER0351.pbix?dl=0

Above link is my PBI 

Paul

[AlexisOlson · Accepted solution]

That's because it needs to be defined as a new calculated table, not a measure or calculated column. See the example I attached previously.

[admin11]

@AlexisOlson 

many thank

[VahidDM]

@admin11 

Can you share your file on https://wetransfer.com/ pleae?

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[admin11]

@VahidDM 

My PBI sample file

https://www.dropbox.com/s/rf7o60s9y0yhxpd/show%203%20or%204%20yr.pbix?dl=0

Paul

[VahidDM]

@admin11 Paul,

Download and check this file:
https://gofile.io/d/f4TKg8

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[VahidDM]

@admin11 

Have you checked this file: https://gofile.io/d/f4TKg8

let me know if you face with any issue.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[admin11]

@VahidDM 

I get below error :-

[VahidDM]

@admin11 

Set No. Year is a parameter.

define a parameter and then use that in the formula same as my file.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/