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/...
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
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
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    AlexisOlson  that worked! thanks, good call on the blank

     

  • dospencer's avatar
    dospencer
    Frequent Visitor
    4 years ago

    Hi,

     

    I am having the same issue of multiple rows appearing when I apply the FORMAT function to conditionally format a specific row in a matrix visual as percentage. I tried adding the blank check but it didn't fix the issue for me. The issue does no occur if I remove the FORMAT function.

     

    This is my DAX measure that returns the correct number of rows in the matrix. It blows up the number of rows once I change the line highlighted in red to 

    VAR _percentDiscount =
    FORMAT(DIVIDE(_subtotalFinancialAid,_subtotalTuitionRevenue),"Percent")
     
    Selected Measure =
    VAR _maxline =
    MAX ('DWH Finance_Organization_Dim'[Line_Order] )

    VAR _subtotalTuitionRevenue =
    CALCULATE(SUM('ODS Net_Tuition'[Tuition_Billed])
    , ALL('DWH Finance_Organization_Dim'[Title])
    ,'DWH Finance_Organization_Dim'[Line_Order] <= 3)
        
    VAR _subtotalNetTuition =
    CALCULATE(SUM('ODS Net_Tuition'[Tuition_Billed])
    , ALL('DWH Finance_Organization_Dim'[Title])
    ,'DWH Finance_Organization_Dim'[Line_Order] <= 5)

    VAR _subtotalFinancialAid =
    CALCULATE(SUM('ODS Net_Tuition'[Tuition_Billed])
    , ALL('DWH Finance_Organization_Dim')
    ,'DWH Finance_Organization_Dim'[Title] = "Financial Aid Awards")

    VAR _percentDiscount =
    DIVIDE(_subtotalFinancialAid,_subtotalTuitionRevenue)

    VAR _subtotal =
    SWITCH(TRUE(),

         _maxline = 3, _subtotalTuitionRevenue
         ,_maxline = 4, _subtotalFinancialAid*-1
            ,_maxline = 5, _subtotalTuitionRevenue - _subtotalFinancialAid
            ,_maxline = 6, _percentDiscount
            ,SUM('ODS Net_Tuition'[Tuition_Billed])
            )
    RETURN _subtotal
    • AlexisOlson's avatar
      AlexisOlson
      Super User
      4 years ago

      Maybe try something like this:

      Selected Measure =
VAR _maxline =
[...]
VAR _total = SUM ( 'ODS Net_Tuition'[Tuition_Billed] )
VAR _subtotal =
    SWITCH (
        TRUE (),
        _maxline = 3 && NOT ISBLANK ( _subtotalTuitionRevenue ),
			FORMAT ( _subtotalTuitionRevenue, "0.00" ),
        _maxline = 4 && NOT ISBLANK ( _subtotalFinancialAid ),
			FORMAT ( _subtotalFinancialAid * -1, "0.00" ),
        _maxline = 5 && NOT ISBLANK ( _subtotalTuitionRevenue - _subtotalFinancialAid ),
			FORMAT ( _subtotalTuitionRevenue - _subtotalFinancialAid, "0.00" ),
        _maxline = 6 && NOT ISBLANK ( _percentDiscount ),
			FORMAT ( _percentDiscount, "0.00%" ),
        NOT ISBLANK ( _total ),
			FORMAT ( _total, "0.00" )
    )
RETURN
    _subtotal
      • dospencer's avatar
        dospencer
        Frequent Visitor
        4 years ago

        AlexisOlson Thank you so much. That fixed my issue.

         

        I added an alternate result to the DIVIDE function so that it returns 0 where the devisor was 0 otherwise those were being formatted as currency.

        VAR _percentDiscount =
        DIVIDE(_subtotalFinancialAid,_subtotalTuitionRevenue,0)