Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Switch function causing many rows

Hi all, 

 

I am using the following DAX measure to format the sum of sales & for some reason when I drop the measure in a visual a bunch of lines of data appear, any ideas on why that would happen/ how to fix it? Thanks!

 

Parent Annual Revenue =

var _1 = calculate(SUM('Sales Table'[Parent Annual Sales]))

var _2 = SWITCH (
TRUE (),
VALUE( _1) >= ( 10 ^ 8 ), FORMAT(_1, "$#,0,,,.## B; ($#,0,,,.## B)"),
VALUE( _1) >= ( 10 ^ 5 ), FORMAT(_1,"$#,0,,.## M; ($#,0,,.## M)"),
VALUE( _1) >= 1000, FORMAT(_1,"$#,0,.## K; ($#,0,.## K)"),
VALUE( _1) < 1000, "-"
)
return _1
Need Help
Show and Tell
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    Hmm. Does adding a blank check help at all?

     

    Parent Annual Revenue =
VAR _1 =
    CALCULATE ( SUM ( 'Sales Table'[Parent Annual Sales] ) )
VAR _2 =
    SWITCH (
        TRUE (),
        ISBLANK ( _1 ), BLANK (),
        VALUE ( _1 ) >= ( 10 ^ 8 ), FORMAT ( _1, "$#,0,,,.## B; ($#,0,,,.## B)" ),
        VALUE ( _1 ) >= ( 10 ^ 5 ), FORMAT ( _1, "$#,0,,.## M; ($#,0,,.## M)" ),
        VALUE ( _1 ) >= 1000, FORMAT ( _1, "$#,0,.## K; ($#,0,.## K)" ),
        VALUE ( _1 ) < 1000, "-"
    )
RETURN
    _2

7 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    Can you explain what you mean by "a bunch of lines of data appear"? What are you seeing and what do you expect to see?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      when I add this measure to a table visual with anyother metric (lets say company name) I get a list of all the company names. I expect to see only one value for the filtered company name

      • AlexisOlson's avatar
        AlexisOlson
        Super User
        4 years ago

        Hmm. Does adding a blank check help at all?

         

        Parent Annual Revenue =
VAR _1 =
    CALCULATE ( SUM ( 'Sales Table'[Parent Annual Sales] ) )
VAR _2 =
    SWITCH (
        TRUE (),
        ISBLANK ( _1 ), BLANK (),
        VALUE ( _1 ) >= ( 10 ^ 8 ), FORMAT ( _1, "$#,0,,,.## B; ($#,0,,,.## B)" ),
        VALUE ( _1 ) >= ( 10 ^ 5 ), FORMAT ( _1, "$#,0,,.## M; ($#,0,,.## M)" ),
        VALUE ( _1 ) >= 1000, FORMAT ( _1, "$#,0,.## K; ($#,0,.## K)" ),
        VALUE ( _1 ) < 1000, "-"
    )
RETURN
    _2