Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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!
@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.
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]
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingAn 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)
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI 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:
i need a table only with Machines by MachineType filter (not in red square)
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@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 🤔
@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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@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