Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
kalkhudary
Helper III
Helper III

Isblank if nothing selected on a visual

 
Hello Community, 
 
I am struggling to write this formula that shows the last year price index when I click on the visual for a particular city and if I don;t click on any, it should give me blank. Can someone help me rewrite it so it works.
 
Last Year Price index = IF(ISBLANK([Current Price Index],Blank(),calculate([Current Price Index],DATEADD(Calender[Date], -1,YEAR))))
 
Thanks indeed.
1 ACCEPTED SOLUTION
jennratten
Super User
Super User

Hello - You were really close.  The measure below works for me.  Basically, you should create a separate measure for LastYearPriceIndex with the basic logic of calculating the amount for the prior year (excluding the blank evaluation).  Then create another measure which is the one that goes in the visual (example below).  You will need to update the table and column name in the SelectedCity variable and also update measure name in the SelectedCalculation variable.

Last Year Price Index for Display = 
VAR SelectedCity = SELECTEDVALUE( DimTableName[CityColumnName],blank() )
VAR SelectedCalculation = [YourMeasureName]
VAR Result = if ( SelectedCity = blank(), blank(), SelectedCalculation )
RETURN
    Result

 

View solution in original post

5 REPLIES 5
kalkhudary
Helper III
Helper III

@jennratten That worked like magic 🙂 Quick Question: is there a way that instead of displaying the word (Blank) to keep it with no words just nothing shows up. Curious to know if we can apply something to the dax to make it show nothing in terms of wording or maybe a dash (-)

There are a couple of things you can do. 

  1. Add conditional formatting to make the font color the same as the background if the value is equal to 0 or blank.
  2. Change the return result from blank() to custom text of your choosing.

Actually, I figure it out, just change blank() to "-".
 
Last Year Price Index for Display =
VAR SelectedCity = SELECTEDVALUE( Geography[City],blank() )
VAR SelectedCalculation = [Last Year Price index]
VAR Result = if ( SelectedCity = blank(), "-", SelectedCalculation )
RETURN
    Result
 
Thanks so much, you made my day.

You are very welcome!!

jennratten
Super User
Super User

Hello - You were really close.  The measure below works for me.  Basically, you should create a separate measure for LastYearPriceIndex with the basic logic of calculating the amount for the prior year (excluding the blank evaluation).  Then create another measure which is the one that goes in the visual (example below).  You will need to update the table and column name in the SelectedCity variable and also update measure name in the SelectedCalculation variable.

Last Year Price Index for Display = 
VAR SelectedCity = SELECTEDVALUE( DimTableName[CityColumnName],blank() )
VAR SelectedCalculation = [YourMeasureName]
VAR Result = if ( SelectedCity = blank(), blank(), SelectedCalculation )
RETURN
    Result

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors