if statement with the value

[elif]

Hello everyone,

There are some products called "NPI" but after the 4 years (not all the time) they need to be changed as a "Core Product" so when I need to see NPIs for the previous years, if they're still NPI I need to see them but if they expired this year I need to see them as Core Product but the previous years still have to be NPI.

		invoice date
Product	NPI End Date	2018	2019	2020
A	2018	$1,000
B	2019	$5,000	$3,000
C	2022	$2,000	$3,000	$5,000

I tried to write a DAX measurement to calculate these changes and I want to use the result in the IF statement but that's not working correctly but the measurement does.

If the value is 1 the output has to be "NPI' if it is not, get the value from 'Product'[POS type]  (which they're; Core Product, Custom, Identity Product, outofscope)

is grater = VAR maxtable1 = CALCULATE(MAX('Date'[Date])) VAR maxtable2 = CALCULATE(MAX('Product'[NPI End Date])) RETURN IF (maxtable2 >= maxtable1, 1,0)

POS Type New = IF( [is grater] = 1, "NPI", 'Product'[POS type])

By the way, 'Product'[NPI End Date and 'Date'[Date] don't have an active relationship between the columns.

[v-robertq-msft · Accepted solution]

Hi, 

According to your description and sample pictures and screenshots, I can roughly understand where did your error occur. You are using the [is grater] as a measure in the table chart. But you are trying the create a calculated column [POS Type New] to get the value of the measure [is grater]. This may cause the value error in the calculated column [POS Type New].

Therefore, I suggest you to create a new measure [POS Type New1] like this:

POS Type New1 = IF( [is grater] = 1, "NPI", MAX('Product'[POS type]))

Then you can use this measure to be placed into the table chart to replace the original [POS Type New] to check if the issue disappears.

And you can get what you want.

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@elif , Try with this change

is grater = VAR maxtable1 = CALCULATE(MAXX(allselected('Date'), 'DAte'[Date]))
VAR maxtable2 = CALCULATE(MAX('Product'[NPI End Date]))
RETURN IF (maxtable2 >= maxtable1, 1,0)

[elif]

Thank you @amitchandak  but still not working for me. Actually, 'is grater' calculation works very well but when I put the result to the IF statement, I don't know why but it's not getting the right result. "1" should give me "NPI" and "0" should give me the 'Product'[POS type] column's outputs. (like Core Product, Custom etc.)

POS Type New = IF( [is grater] = 1, "NPI", 'Product'[POS type])