Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Measure is breaking my table

Hi! I have a problem where my measure is breaking my table.

 

I will show some screenshots that have the data. Now I will explain:

 

Currently I can make it work, Im having this:

 

This is working as supposed, I have some filters ON and this is what I want to display. BUT I would like to display a "0" instead of blank values, so I thought of adding a "+0" or doing a Coalesce to fix that. But when I do that my table relationships breaks having this (Same filters ON):

 

Is ther a way to fix this? I want to display a 0 instead of a blank value, but if I trying changing it with an IF, Coalesce or "+0" then my table breaks and displays all the info that isn´t suppose to be seen there.

 

 Please send some tips or tricks to this!!

 

 

 

General Comment
Need Help
Show and Tell
Tips and Tricks

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    I think you could use a switch.

     

     

    your measure =
....
RETURN
   SWITCH(
     TRUE(),
     ISBLANK( SUM_ ), 0, //"0" if sum_ is text
     SUM_
    )

     

     

     

    However, i think, if possible, this is better to be a calculated column in your model.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      I tried it and we got the same result:

       

       

       

      I get that the problem is transforming blank values into a value, seems that power bi doesn´t count the blanks as values so I only get the ones I am filtering. But If I change them, then it it breaks. 

       

      Is there a way to manage a measure - relation? O another trick for this?

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Can you try,

         

        your measure =
....
RETURN
   SWITCH(
     TRUE(),
     ISBLANK( SUM_ ), 0, //"0" if sum_ is text
     SUM_ = "", 0,
     SUM_ = " ", 0, 
     SUM_
    )

         

        Also using measures in a table visual with lots of dimensions maybe somewhat confusing. I would try to make a calculated table with all the layout i need. Or you already have all you need, and just 1 calculated column could resolve your issue.

         

  • Erokor's avatar
    Erokor
    Resolver II
    2 years ago

    You are getting a BLANK() instead of a 0, because there is nothing to SUM. The table is empty, thus a BLANK() is shown. Everyone here is correct - the easy way to fix this is to simply add a +0 to your calculation.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Im replying both here Anonymous & Erokor :

     

    My problem is that changing the BLANK() for "0" breaks my table. Adding a "+0" still breaks it, I also tried 

    using
    "RETURN
    " " & ( Sum_ + 0 ) "
    But its not working. Is there a way to transform my BLANK values to 0 without breaking my table?

    Thats the answer I need.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Anonymous really strange for me, 

       

      SUM_ var is number, and summing zero to it, should not break your visual. My other suggestions could do that.

       

      Did you try something step by step?? like starting with less filters, just for a test run??

       

      Create a new measure with:

      VAR ABC = CALCULATE( SUM(MY SP COLUMN), TYPE = "STORY")

      RETURN

      ABC + 0

       

      Just to do some tests with in a context with less filters. And if it works, you could try newer things from there.