Forum Discussion

RichardV's avatar
RichardV
Advocate IV
8 years ago
Solved

Dax: blank values +0 formula

Hi guys,

 

I was wondering if somebody could help me. It is concerning a PBI report with 2 visualisations, and I am working with an SSAS tabular model.

 

Visualisation 1: line graph with date (by day) on the X axis, and amount of files on the Y axis (last 3 years)

Visualisation 2: line graph with months on the X axis, amount of files on the Y axis and years as legend. (last 3 years)

 

Question:

 

I wanted to have a "0" instead of "blank" value for visual 1. That way, I can see for each data if there was one or multiple files per day. Therefore, I used the DAX formula "Measure = [amount of files] + 0", which works great for visualisation 1.

 

However, If I am using this formula for visualisation 2, it shows a line at "0" at november and december 2017. This I do not want to see, because this is a future date with no values.

 

Is there a DAX formula to make a measure to show a "0" for a blank value for each date earlier than today, but which is showing blank values for future days?

 

Help is appreciated, because I am not yet that savvy with DAX. Also keep in mind that the DAX formula should not include creating a calculated column, because I want to make the measure on top of an SSAS tabluar model.

 

Many many thanks in advance!!!

 

Richard

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi RichardV,

     

    You can use IF condition in your measure like:

    Measure = IF(MAX(Table[Date].[MonthNo])<=MONTH(TODAY()),[amount of files]+0,BLANK())

    Remember to enable 'Show items with no data' on chart axis.

     

    Best regards,
    Yuliana Gu

  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi RichardV,

     

    So, currently, with this formula Measure= IF(MAX('Table'[Date])>DAY(TODAY()) . [Amount of files]+0, BLANK()), you can always achieve the desired result, right?

     

    Regards,
    Yuliana  Gu

4 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi RichardV,

     

    You can use IF condition in your measure like:

    Measure = IF(MAX(Table[Date].[MonthNo])<=MONTH(TODAY()),[amount of files]+0,BLANK())

    Remember to enable 'Show items with no data' on chart axis.

     

    Best regards,
    Yuliana Gu

    • RichardV's avatar
      RichardV
      Advocate IV
      8 years ago

      Hi v-yulgu-msft,

       

      Thanks a lot for your comment.

       

      However, it deed not seem to work. Perhaps it has to do with the fact that I don't have the function with "show items with no data' because I create a measure on top of a SSAS tabular model.

       

      The strangest thing is, that I tweaked a little with the formula, and I have seem to make it work. Unfortunately, I do not have an idea why this works.

       

      The only thing that I did, is change "<=" into ">". So the formula becomes:

       

      Measure= IF(MAX('Table'[Date])>DAY(TODAY()) . [Amount of files]+0, BLANK())

       

      I don't have an explanation for why this works. Can I use this safely, or is there any error in this?

       

      Best,

       

      Richard

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Microsoft Employee
        8 years ago

        Hi RichardV,

         

        So, currently, with this formula Measure= IF(MAX('Table'[Date])>DAY(TODAY()) . [Amount of files]+0, BLANK()), you can always achieve the desired result, right?

         

        Regards,
        Yuliana  Gu