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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

6 REPLIES 6
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.


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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.