Forum Discussion

rischav's avatar
rischav
Frequent Visitor
5 years ago
Solved

If function to return values in measure based on Slicer selection

Hi all,

 

I have been looking for a solution all day long on the forum, but could find one that worked for me.

I have two columns Fiscal Year and Buget ref as seen in the screenshot. Fiscal year is the financial year and budget Ref is the year the expense was made by an organization

 

As seen in the screenshot I have the fiscal year 2019 chosen in the slicer and hence you see 2019 only in the fiscal year column and corresponding budget ref for those expenses.

What I need is: when I select a fiscal year in the slicer, all budget ref row values that match with the fiscal year, shall show CY and ones that do not shall be PY in the 3rd column. the blank fiscal year rows should be avoided.  Please see example of the expectation below

Fiscal YearBudget RefBudget year classification
20192017PY
20192016PY
20192019CY
20192019CY

 

It would great if someone could help

 

Thanks a lot

  • rischav, Try a new measure like

    Budget year classification = if(max(Table[Fiscal Year]) = max(Table[Budget Ref]) ,"PY","CY")

2 Replies

  • rischav, Try a new measure like

    Budget year classification = if(max(Table[Fiscal Year]) = max(Table[Budget Ref]) ,"PY","CY")