Ignore if total count is zero

[abhilash]

Hi Power BI Experts,

I want to be able to ignore a measure from showing in a barchart if the total count is zero. I'm currently filtering it by the value "A":

aCount = CALCULATE ( COUNTROWS('<tablename>'), '<tablename>'[column] = "A").

How can I fix this function to achieve that?

Thank you in advance,

Abhilash

[malagari]

You can store the aCount as a DAX variable, and then return BLANK() if it is equal to zero.  Like so:

aCount = 

var aCountValue = CALCULATE( COUNTROWS(Table), Table[Column] = "A" )

return
IF( aCountValue = 0, BLANK(), aCountValue )

[abhilash]

Thank you for the recommedation malagari. But, this doesn't solve the problem I have.

In this chart, I'm using 3 measures. I wanted to able to line the blue and red bars side-by-side in the absence of a value for the green bar.

[Ashish_Mathur]

Hi,

Share the link from where i can download your file.

[AlbertoFerrari · Accepted solution]

It's really weird as a solution... but you can do something like this:

M1 = 
IF ( 
    [M1] = 0, 
    IF ( 
        [M2] = 0,
        [M3],
        [M3]
    ),
    [M1]
)

In this way, [M1] (your first measure) returns the value of M1, M2 or M3, the first non blank.

Then you repeat a similar logic in the other measures, taking into account that M2 might have been shown in M1, if M1 was blank... thus it ends up in a set of somewhat complex conditions that draw the chart in the right place.

Nevertheless, even if you could draw the bars one beside the other, at the end the colors will not match because the value of M1 might be M1, M2 or M3, from the DAX point of view, but from the Power BI visual one, it will always be a blue line.

Thus, my guess is that your only option is to write a custom visual to obrain such a behavior, even if you could solve it in DAX, it would be a dead end, because Power BI prevents you from using it as a good solution.

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com