Distinguishing 0/0 values from 0/number values

[Anonymous]

hello everyone,
i am having a problem of distinguishing a 0/0 or null / null values from a 0/number values on a dashboard as they both shows as an empty cell, i would like to see the 0/2 valuse as a 0 and the null / null or 0/0 values as an empty cells , is there anyway to do that.
thanks .

[PaulDBrown · Accepted solution]

See if this works for you (I've created separate measures for the numerator and denominator because it's easier to read, but you can do it all in one measure if you wish)

Divide Sales by Target = 
SWITCH(
    TRUE(),
    AND(ISBLANK([Sum Sales]), [Sum Target] >0), 0,
    [Sum Target] >0,
    DIVIDE([Sum Sales], [Sum Target], 0),
    DIVIDE([Sum Sales], [Sum Target]))

If you want blank when the target is blank even if there is a value for sales, you can  use this measure:

Divide Sales by Target =
SWITCH (
    TRUE (),
    [Sum Target] > 0, DIVIDE ( [Sum Sales], [Sum Target], 0 ),
    DIVIDE ( [Sum Sales], [Sum Target] )
)

BTW, you can simplify your measures by using the IN expression:

https://www.sqlbi.com/articles/understanding-the-in-operator-in-dax/ 

So, here is the equivalent to the measure your are using:

Sum using || =
CALCULATE (
    SUM ( fTable[Sales] ),
    fTable[Product] = "A"
        || fTable[Product] = "B"
        || fTable[Product] = "c"
)

and here is the simpler measure using the IN expression:

SUM using IN = 
CALCULATE([Sum Sales], fTable[Product] IN {"A", "B", "C"})

and here is the comparison in a table visual:

I've attached a sample PBIX file

[PaulDBrown]

Assuming the [RID ENI] measure is the division of [measure 1] by [measure 2], try:

RID ENI =
IF (
    [measure 2] > 0,
    DIVIDE ( [measure 1], [measure 2], 0 ),
    DIVIDE ( [measure 1], [measure 2] )
)

[pi_eye]

Hi HifNi93

PowerBI will present the results of the numbers differently from a division, depending on if you use the "/" operator, or the divide() function.

See below experiment using some randomly generated data:

(1) Straight column (as opposed to measure):  N/D = data[Numerator]/data[Denominator]

(2) Measure created using the "/" operator: sumN/sumD = sum(data[Numerator])/sum(data[Denominator])

(3) Measure created using the DIVIDE() function: Divide SumN SumD = divide(sum(data[Numerator]), sum(data[Denominator]))  

The "/" operator returns the NaN or infinity, whereas the Divide() function cleans these results and returns blank It doesn't not matter whether it is a measure or Calculated column.

This means you can use the result of the "/" to tailor what output is returned.

This switch statement interprets the text returned by sumN/sumD and displays a custom string:

Switch sumN/SumD = switch(convert([sumN/sumD],string) ,"-nan(ind)","~ Not a number~","inf","~Infinity~","","Blank",[sumN/sumD]   )    

Note the values for NaN and Infinity are different when converted to a string!

Hope this gives you a few options.

Pi

[Anonymous]

thanks for your answer but actually nothing woks as i am using a calculate sum when i converted the results to strings it's still blanks 

[pi_eye]

Can you tell how bo_value is calculated? I think the NaNs/ blanks are originating from there.

[Anonymous]

Bo_value is an orginal cloulmn, its numbers between 1-10

[pi_eye]

Looks like the blanks are coming from the original source. PowerBI would need more information in order to determine what type of non-real number it is.

Does the source contain the denominator? If the denonominator is present, then that could be compared with a switch statement instead.

[Anonymous]

The Denominator is also the Bo_value but with a different filter , 

[pi_eye]

Hi - in that case you just need to point an IF() statment at the denominator. Something similar to:

Testmeasure = if( ISBLANK( sum(data[Denominator])),"no value for denominator",if(sum(data[Denominator])==0,"zero","has value")) but replacing the returned values with what you would like in the column