Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there,
I have a table carrying integers which contains blanks. Ideally I would prefer these blank values to contain 0. Can I create a measure that acheives this?
Thanks
Solved! Go to Solution.
I found a quick and easy solution here if anyone is interested:
https://community.powerbi.com/t5/Desktop/show-items-with-no-data-as-0-instead-of-blanks/td-p/113160
See Seans's solution specifically. It's very similar to one of amitchandak's reccomendations.
Kind regards
Hi @Anonymous ,
Yes,it is also supported if the value is a measure,see below:
If your issue is solved,could you pls mark the reply as answered to let more people find the solution?
Much appreciated.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hi @Anonymous ,
If you wanna replace all the blanks in a table into 0,you'd better do it in query editor using below M codes:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUTJUitWJVgIyjMAMYyALJmIMZpgAWaZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}}),
#"Replaced Value1" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,Table.ColumnNames(#"Changed Type"))
in
#"Replaced Value1"
And you will see:
Using measure can only replace the values in a certain column,see below:
Measure = IF(ISBLANK(MAX('Table (2)'[Column1])),0,MAX('Table (2)'[Column1]))
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hey Kelly, appreciate the help.
I tried using your M Code solution but it makes all my visuals go blank.
In regards to
Measure = IF(ISBLANK(MAX('Table (2)'[Column1])),0,MAX('Table (2)'[Column1]))
Will that measure work if the data in my visual is also a measure? so instead of 'Table (2)'[Columnx] its
'Table (2)'[Measurex]
Thanks
I found a quick and easy solution here if anyone is interested:
https://community.powerbi.com/t5/Desktop/show-items-with-no-data-as-0-instead-of-blanks/td-p/113160
See Seans's solution specifically. It's very similar to one of amitchandak's reccomendations.
Kind regards
Hi @Anonymous ,
Yes,it is also supported if the value is a measure,see below:
If your issue is solved,could you pls mark the reply as answered to let more people find the solution?
Much appreciated.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
@Anonymous , Add +0 , but will create a complete left join
New measure = [measure] +0
Another example of controlled +0
0 between range //only between range
Measure = var _1= SUM(Opportunity[Opportunity count]) +0
var _min = minx(ALLSELECTED('Calendar'), 'Calendar'[Date])
var _max = maxx(ALLSELECTED('Calendar'), 'Calendar'[Date])
return
CALCULATE(if(max('Calendar'[Date]) <_min || max('Calendar'[Date]) >_max , BLANK(), _1))
HI there,
I dont know how to embed this solution into my power bi report.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
67 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |