Forum Discussion

klehar's avatar
klehar
Helper V
1 year ago

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

 

Need Help

11 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    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.

  • danextian's avatar
    danextian
    Super User
    1 year ago

    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.

     

     

    • klehar's avatar
      klehar
      Helper V
      1 year ago

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

      That isnt working with the allexcept

      • klehar's avatar
        klehar
        Helper V
        1 year ago

        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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.