Forum Discussion

aTChris's avatar
aTChris
Icon for Resolver I rankResolver I
6 years ago

Analyze in Excel - cant show items with no data

Hi, I wonder if anyone has found a solution for this.

I have a Power BI report which shows contracted revenue per per per asset within a matrix. Using show items with no data I can see assets without any revenue.

 

 

 

 

 

The issue is when I connect to the dataset using Analyze in excel. The option for show items with no data is grey'd out.

 

 

 

 

 

 

 

 

 

 

 

 

 

The revenue is measure, ive tried replacing blanks with a 0 but that breaks any slicer applied to the matrix.

Contract Revenue =
IF (
    CALCULATE ( SUM ( Contracts[Revenue] ) ) = BLANK (),
    0,
    CALCULATE ( SUM ( Contracts[Revenue] ) )
)

 

Need Help

3 Replies

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

    HI aTChris 

     

    Try this

     

    Contract Revenue =
SUM ( Contracts[Revenue] ) + 0

     

    The Name column should be in a Dimension Table that has a relationship with Contracts

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

     

    • aTChris's avatar
      aTChris
      Icon for Resolver I rankResolver I
      6 years ago

      Mariusz thanks but that breaks slicers like the other measure.

      I think im going to have to create a 0 entry within the data for every assets and month. 

      • rayishome's avatar
        rayishome
        Icon for Resolver I rankResolver I
        6 years ago

        Found a workable solution while reviewing the documentation on how PBI deals with missing values.  

         

        Measure = IF(ISBLANK(SUM('Table'[Cnt])),IF(CALCULATE(COUNTROWS('DimTableWithMissingValues'))>0,0),SUM('Table'[Cnt]))
         
        Since there is no explicit measure and the two tables are directly related, Power BI attempts to inject a measure to constrain the combinations that result. In this case, Power BI injects a CALCULATE(COUNTROWS('Product')) measure, which should not be blank, since Product is the table that is common to both tables.