cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Visual showing blank rows using SELECTEDVALUE and FORMAT Function

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%)")))

1 ACCEPTED SOLUTION
v-jiascu-msft
Microsoft
Microsoft

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-jiascu-msft
Microsoft
Microsoft

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Thank you su much for this!

Anonymous
Not applicable

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!

Hi @Anonymous,

 

Please refer to format-function-dax.

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

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.