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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
klehar
Helper V
Helper V

Data not available

Hi,

Please see the image below. I have some data for december. But january data is not available yet. 

So im hardocding it in the return statement saying

 

if(isblank(result),0, result)

Is there a way I can actually show something like "Not Available".

If I put it text in the above statement, the visual would break

klehar_0-1736338229090.png

 

11 REPLIES 11
v-yajiewan-msft
Community Support
Community Support

Hi @klehar,

 

I'm checking how the things are going on about this issue. Whether the answer helps you?

 

If there is any problem, please feel free to let me know.

danextian
Super User
Super User

hi @klehar 

You can apply a dynamic format string to the % Quote Esc QTD measure. This approach retains the same data type but changes the format based on specific conditions. In the screenshot below, I’m applying a dynamic format string to the Sum of Value measure, depending on the value of the Sum of Value by Month measure. The purpose is to ensure that formatting is only applied if the total monthly value is zero and not to all zero values from past months. In your case, this will display "N/A" for 0.0% in December if the dynamic format string is based on the same measure in your table. Alternatively, you could use the FORMAT function, but that would convert the result into a text string. Instead, use "0.00%" as the format string rather than "#,#". Please refer to the attached PBIX file for details.

 

danextian_0-1736339458894.png

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

@danextian thanks for the response. However in real life sceanrio my date is conencted through a date dimension.

klehar_0-1736340337765.png

That isnt working with the allexcept

In my date table I have 4 values, included 202502 feb as well against which there is not even a 0entry in the fact table

Apply ALLEXCEPT to your dates table. I applied that a different table because no sample data was provided and whipped up something for the sake of illustration. Also, your showing 0% in your viz so apply ALLEXCEPT to the same measure as that that is measure you'll want to apply dynamic format string to.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

it doesnt work on feb that has no data (not even 0)

klehar_0-1736340963253.png

 

Hi @klehar , hello all, thank you for your prompt reply!

I could not reproduce your issue with the date dimension table, please check the update pbix:

vyajiewanmsft_0-1736411234537.png

 

Best regards,

Joyce

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

 

agreed with you ,great solutions 

klehar
Helper V
Helper V

can you share the PBI please

Can you share the sample data




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






bhanu_gautam
Super User
Super User

@klehar , Create a measure to check if the data is available.

JanuaryData =
IF(
ISBLANK(SUM('YourTable'[JanuaryColumn])),
BLANK(),
SUM('YourTable'[JanuaryColumn])
)

 

Then, create another measure to handle the display logic:

DisplayJanuaryData =
IF(
ISBLANK([JanuaryData]),
"Not Available",
FORMAT([JanuaryData], "0")
)

 

Go to the matrix visual and select the JanuaryData measure.
Apply conditional formatting based on the value of JanuaryData.
Set the formatting rule to display "Not Available" when the value is blank.




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.