Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 Fu...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    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

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago
    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's avatar
      v-jiascu-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago

      Hi Anonymous,

       

      Please refer to format-function-dax.

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

       

      Best Regards,

      Dale

  • bertalan_ronai's avatar
    bertalan_ronai
    Icon for Advocate I rankAdvocate I
    5 years ago

    Thank you su much for this!

  • RobbLewz's avatar
    RobbLewz
    Icon for Helper II rankHelper II
    3 years ago

    Are microsoft not treating this as a bug?

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


    • IP_Kornel's avatar
      IP_Kornel
      Frequent Visitor
      2 years ago

      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.