Applying an if-then to a measure/calculated column?

[RMDNA]

I have a calculated column for measuring Revenue that ignores any changes to "Year" in a different table:

Revenue Won = 
CALCULATE(
 SUM('Table 1'[Revenue]),
 TREATAS({("Win")}, 'Table 1'[Status]),
 ALL(Table 2[Year])
)

However, I need to filter that value by something else depending on the value of that Year column (i.e. Year can't directly affect it, but something else needs to if Year changes).

e.g.

If Year = FY16, Revenue Won = $100M

if Year = FY17, Revenue Won = $100M (hence the ALL())

However,

if Year = FY16, add Filter A to Revenue Won

if Year = FY17, add Filter B to Revenue Won

Any tips?

[GilbertQ]

Hi @RMDNA

Could I ask why you cannot create a relationship between Table1 and Table2?

Then you could simply create a measure, and due to the relationships if someone filters or clicks on Filter A, it will then simply apply the filter?

Or with the relationship in place it will be a lot easier to create a measure to do what you require.

[RMDNA]

The two tables are connected, but through three other tables and various keys. This is a massive report and a mess of a schema.

Table 1 [Revenue] -- <Table> -- <Table> etc...  -- Table 2 [Year]

And I'm fine with Year affecting every other value on Table 1 except Revenue.

All I need now is to say "If Year changes to X, apply this filter to Revenue."

[GilbertQ]

Hi @RMDNA

If the tables are connected, then could you not possible create the following measure based on your example

My Measure =
IF('Table1'[Year] = 2016,[Sales],CALCULATE([Sales],'TableName'[WonOrLost] = "WON")

[RMDNA]

I got:

"A single value for column 'Year' in table 'Table 1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

'Year' is in a date table, so there's 365 Year '2016s.'

[GilbertQ]

Hi @RMDNA

What you then might need to do is change it to using a Variable with the SELECTEDVALUE formula, but without having a look at your data it is quite a challenge to understand what you are trying to achieve!

Just change your measure below to use this and replace the initial part with the SelectedYear

My Measure =
VAR SelectedYear = SELECTEDVALUE('Table1'[Year],"Nothing Selected")
RETURN
YOUR CALCULATION

[RMDNA]

Going off your answer, I was successful using:

Measure = 
VAR SelectedYear = SELECTEDVALUE('Date'[Fiscal Year],"Nothing Selected")
RETURN
IF(SelectedYear = 2018,
	CALCULATE([Revenue FY Isolated],
		TREATAS({("FY18")}, 'Revenue'[Due Date])))

My only other question: how would I incorporate an "else" into this, to chain together more years? At least for this purpose, I need to add a "Selected Year" for 2016 and 2017.

Thanks for sticking with this. I know it's a mess of a problem.

[GilbertQ · Accepted solution]

Hi @RMDNA

This measure below should work for you.

Measure 1 = 
VAR SelectedYear = SELECTEDVALUE('Date'[Fiscal Year],MAX('Date'[Fiscal Year]))
VAR SelectedFY = "FY" & RIGHT(SelectedYear,2)
RETURN
CALCULATE([Revenue FY Isolated],
		TREATAS({(SelectedYear)}, 'Revenue'[Due Date])))

What I am doing above is to first get the selected Fiscal Year, and if no Fiscal Year is selected to then get the Max Fiscal Year.

Then in the second VAR I am creating the FY based on what was selected previously. So if 2018 is selected the VAR SelectedFY will return "FY18", and if 2017 is selected it will return "FY17"

That is then dynamically passed through to your measure. So instead of having multiple IF statements or using a SWITCH statement this should work.

[RMDNA]

It worked perfectly. I really appreciate the help - didn't expect anyone to work through it for so long. I definitely learned new DAX functions to look into as well.

Fantastic support. Thank you again.