Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Repleacing blank() with zeros

I have a table with simple SUM measures, but i need to replace blanks with zeros. If I change measure with IF statement , it will remove filter applied to Machine column   withou IF   with ...
Need Help
edhans's avatar
edhans
Community Champion
6 years ago

You don't need to use IF. Use COALESCE. 

Production :=
COALESCE(
    SUM( Production[ProducedParts] ),
    0
)

But you will need to explain what you mean by the machine filter. What is the filter? If you are filtering for blank, then yeah, it won't work when you swap blanks for zeros. Please provide a screenshot if possible of the filter, and how it relates to the data.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    An first printscreen you can see only some of Machines (filter applied at filter pane), and all machines at second printscrees (filter is still on) because there is 0 in Scraps of All % measure, but i need to display only Machines choose in filter

    (not check on this printscreen)

    • edhans's avatar
      edhans
      Community Champion
      6 years ago

      Ok, I'm sorry, but I am still not understanding.

       

      "An first printscreen you can see only some of Machines (filter applied at filter pane)" - Yes, but I don't know what that filter is. You provided a screen shot of a filter pane that has no filter selections.

       

      "and all machines at second printscrees (filter is still on) because there is 0 in Scraps of All % measure"  - I don't see how your 0 scraps in all % meaure applies to the filter. If that measure returns blank, then by default you may/may not see machines because of how you have the data set. You can see in the image below you can show/hide items with "no data" which means blanks.

       

      " but i need to display only Machines choose in filter" - What are you expecting to see exactly? That is a filter of machines. That is what you should see, only machines. I am not understanding.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        I was wrong. thank you for you patience.

        example:

        i have two filter dimension: Machine Type, Machines (1:* relationship)

         

        Table ("Machine" is from Machines table, filtred by MachineType table)

        results:

        I need 0's in all other colums for Drill01, WB01, WB04, WB08, WB12 machines

         

        so if i change measure for Scraps of All %

        from measure:

        Scraps of All %:= DIVIDE( [Scraps], [All])

         

        to measure:

        Scraps of All %:= COALESCE(DIVIDE( [Scraps], [All]), 0)

        (thank you for COALESCE)

         

        result is: