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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

5 REPLIES 5
v-jiascu-msft
Employee
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.


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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.