Visual showing blank rows using SELECTEDVALUE and FORMAT Function

[Anonymous]

I have a couple of matrix visuals that I am trying to condesnse into one using a slicer and SELECTEDVALUE to change the data in the table.  I was trying to clean up with the output with the FORMAT Function.  That seems to be causing the issue of adding blanks for rows and columns without data.  Not "blank", just nothing there.  Any thoughts on how to make that disappear?  It would be hard to share the model at this point, but here is the measure causing the issue.  I have tested this without the FORMAT Function and it does not cause the error, so I am pretty sure that is it.  Seems like there should be some way to use ISBLANK, but I am missing something.  Thanks. 

CB Data =
if(
SELECTEDVALUE('CB Selection'[CB Selection])="Eaches",FORMAT([CB Eaches],"#,##0;(#,##0)"),
if(
SELECTEDVALUE('CB Selection'[CB Selection])="Net Sales",format([CB Contract Value],"$#,##0;($#,##0)"),FORMAT([CB MS%],"0.0%;(0.0%)")))

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

We only need to add a condition like below.

CB Data =
IF (
    SELECTEDVALUE ( 'CB Selection'[CB Selection] ) = "Eaches",
    FORMAT ( [CB Eaches], "#,##0;(#,##0)" ),
    IF (
        SELECTEDVALUE ( 'CB Selection'[CB Selection] ) = "Net Sales",
        FORMAT ( [CB Contract Value], "$#,##0;($#,##0)" ),
        IF (
            ISBLANK ( 'CB Selection'[CB Selection] ),
            BLANK (),
            FORMAT ( [CB MS%], "0.0%;(0.0%)" )
        )
    )
)

Best Regards,

Dale

[RobbLewz]

Are microsoft not treating this as a bug?

Using if is blank over a large dataset isn't practical.

[IP_Kornel]

I agree, this is crazy. In my case I need the percentage forma. Either I have an empty row that I need to filter out later from visual or check for blanks and leave them as they are and rest format to percent values. That second options makes for example one row with 5.43% and next row is 0.00 without % sign that looks plain stupid.

[bertalan_ronai]

Thank you su much for this!

[Anonymous]

CB Data =
IF (
    SELECTEDVALUE ( 'CB Selection'[CB Selection] ) = "Eaches",
    FORMAT ( [CB Eaches], "#,##0;(#,##0)" ),
    IF (
        SELECTEDVALUE ( 'CB Selection'[CB Selection] ) = "Net Sales",
        FORMAT ( [CB Contract Value], "$#,##0;($#,##0)" ),
        IF (
            ISBLANK ( 'CB MS%] ),
            BLANK (),
            FORMAT ( [CB MS%], "0.0%;(0.0%)" )
        )
    )
)

Had to make one change to get it to work, but that did the trick!  What does it only need to be applied to that measure?

THANKS!

[v-jiascu-msft]

Hi @Anonymous,

Please refer to format-function-dax.

>>>If value is BLANK() the function returns an empty string.

Best Regards,

Dale