cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rischav
Frequent Visitor

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

Fiscal Year.pngSlicer.png

 

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

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@rischav, Try a new measure like

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
rischav
Frequent Visitor

It magically worked! 🙂 

amitchandak
Super User IV
Super User IV

@rischav, Try a new measure like

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors