Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 Employee
Microsoft Employee

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

6 REPLIES 6
v-jiascu-msft
Microsoft Employee
Microsoft Employee

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.

Are microsoft not treating this as a bug?

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


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.

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors