Repleacing blank() with zeros

[Anonymous]

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 IF in Scraps of All %  measure. it works, but now this 0 is ignoring filter in Machine column

Production:= SUM( Production[ProducedParts] )

Total Scraps:= SUM( Scrap[pocet] )

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

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

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

thank you

[v-diye-msft]

Hi @Anonymous 

If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly. If not, please kindly elaborate more. thanks!

[jdbuchanan71]

@Anonymous 

You can use a trick of DIVIDE to exclude the rows where there is no [Production] and then you can use the IF like so:

Scrap of All % = DIVIDE([Production],[Production]) * IF ( [Scraps] = 0, 0, DIVIDE ( [Scraps], [All], 0 ) )

DIVIDE([Production],[Production]) returns a BLANK() if there is not [Prodution] and a 1 if there is [Prodcution].  BLANK * a value = BLANK so then the IF only shows on lines where there is production and the % is 0 when there is no [Scraps] 

you can see in my data I have more machines but some have to [Production].  With just the IF I get the 0 on rows that should be empty but the corrected measure from above give me a blank on those rows so they will drop out when I am only showing the good measure.

[jdbuchanan71]

We can make it a bit better and avoid calcing measures multiple times using a VAR.

Scrap of All % =
VAR _All = [All]
VAR _Scraps = [Scraps]
RETURN 
    DIVIDE ( _All, _All ) * IF ( _Scraps = 0, 0, DIVIDE ( _Scraps, _All, 0 ) )

 I also changed it to check the [All] rather than [Prodution]

[edhans]

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]

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]

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]

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]

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

[edhans]

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]

@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]

@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.

[Anonymous]

@edhans here is an example file

https://www.uschovna.cz/zasilka/ZRT6735A7CEI7IE9-93X/  

If you add Scraps % measure to table, it will repeat all machines to all machinetypes. What is happend with relationship?

i dont understand why

thank you