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, I need to exlude BLANK's from a calculation, but cant't find the righ way. I want to create a calculated column to be exact, MIN from two other column, but when one of them is blank, the whole calculation returns blank. How do I avoid that? Thx in advance!
Solved! Go to Solution.
Well this is kinda silly but here goes
Column = IF ( ISBLANK ( Table1[Column1] + Table1[Column2] ); BLANK (); IF ( ISBLANK ( Table1[Column1] ); Table1[Column2]; IF ( ISBLANK ( Table1[Column2] ); Table1[Column1]; IF ( Table1[Column1] <= Table1[Column2]; Table1[Column1]; Table1[Column2] ) ) ) )
I realize this request is a couple years old, but I came here yesterday looking for the same answer and managed to come up with a solution that I was rather proud of so I thought I would share in case it helps somebody else in our situation. Many thanks to sqlbi.com for the guidance!
The idea is, you put your values into a column, and then grab the first (minimum) non-blank value using the FIRSTNONBLANK function. Simple!
Example:
FIRSTNONBLANK({[Value1],[Value2],[Value3]},1)
I needed to find the minimum value across 5 columns where a blank was possible but I needed blanks to be ignored. This solution and the FIRSTNONBLANK formula is exactly what I needed. Thanks for the example including the curly { } brakets.
Well this is kinda silly but here goes
Column = IF ( ISBLANK ( Table1[Column1] + Table1[Column2] ); BLANK (); IF ( ISBLANK ( Table1[Column1] ); Table1[Column2]; IF ( ISBLANK ( Table1[Column2] ); Table1[Column1]; IF ( Table1[Column1] <= Table1[Column2]; Table1[Column1]; Table1[Column2] ) ) ) )
Could you put an example with more than 2 columns?
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
50 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |