cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## ISBLANK() or Measure +0 not working

hello!

I am trying to substitute the blank values I have in this small Matrix with 0 for further calculation

I have written this simple measure based on other answers I have found  but it is still not working

Sum - IsBlankMeasure = IF(ISBLANK(SUM('DB'[conta])) ,0, SUM('DB'[conta]))

I have tried also to add a 0 to the measure but didn't work either:

SUM('DB'[conta]) + 0

Here the link to the file

I would like to have for the row "CCC" and "EEE" the column "DELTA"with (-60) for CCC and (-97) for EEE and the same for the column (%).

Any help will be appreciated

1 ACCEPTED SOLUTION
Community Champion

Yes. You need to change the measure to reference the field from the dimension table:

``````% =
VAR currentImporto = [Sum - IsBlankMeasure]
VAR AllImporto =
CALCULATE(
[Sum - IsBlankMeasure],
ALL('DModello'[MODELLO])
)
var ratio =
DIVIDE(currentImporto,AllImporto)
return
ratio``````

Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!

5 REPLIES 5
Super User

The problem doesn't appear to be your measure definition but the behaviour of SUMMARIZECOLUMNS, which is what Power BI uses behind the scenes to generate the output for visuals. I created a dummy measure which simply returns 1 and I couldn't get SUMMARIZECOLUMNS to show a value for CCC or EEE when the year was filtered to 2022.

It seems to me like SUMMARIZECOLUMNS isn't even attempting to execute the measure if the underlying category, in this case MODELLO, does not exist in the filtered data.

I think your only chance is to make sure that you have at least 1 entry in each year for each product. You may be able to use Power Query or DAX to work out which products are missing for a given year and create dummy entries for those products

Frequent Visitor

Here is the picture of the Matrix

Community Champion

You need to create a dimension table for MODELLO and use that in the visual:

Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Frequent Visitor

Thank you for your answer! This is close to what I need...But the measure (%) is going to 100% for all values...

Community Champion

Yes. You need to change the measure to reference the field from the dimension table:

``````% =
VAR currentImporto = [Sum - IsBlankMeasure]
VAR AllImporto =
CALCULATE(
[Sum - IsBlankMeasure],
ALL('DModello'[MODELLO])
)
var ratio =
DIVIDE(currentImporto,AllImporto)
return
ratio``````

Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!