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
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:

 
Anonymous's avatar
Anonymous
Not applicable
6 years ago

 

i need a table only with Machines by MachineType filter (not in red square)

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

    I'm sorry Anonymous - I cannot help. There is no MachineType field in the image so I cannot help you construct a filter based on a field I cannot see or know what is in it. You have to understand I have no idea what your model looks like or your data, beyond your screenshots. You have to provide specific and detailed info.

     

    How to get good help fast. Help us help you.
    How to Get Your Question Answered Quickly
    How to provide sample data in the Power BI Forum

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      edhans 
      of cource. thank you

       

      maybe this help:

       

      all measures:

      Production:= SUM( Production[ProducedParts] )

      Scraps:= SUM( Scrap[pocet] )

      All:= [Total Production] + [Total Scraps]

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

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

      model:

      * MachineCodebook = Machines (from my examples)

      I have put MachineType fied to table. now a see its wrong. By why 🤔

       

       

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

        Anonymous Check out this article on how DAX handles blanks. A number plus a blank = that number, but when you divide with a blank, it returns a blank, not a zero.

         

        You can show/hide items with "no data" which is a blank, using the column setting I gave earlier in a screenshot, or you can cause blanks to return as zero using COALESCE as mentioned before. In its simplest form:

        Measure = COALESCE([Some Other Measure], 0)

        That will return 0 if the [Some Other Measure] returns a blank.