Switch function causing many rows

[Anonymous]

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

[AlexisOlson]

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]

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 · Accepted solution]

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

[dospencer]

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]

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]

@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)

[Anonymous]

@AlexisOlson  that worked! thanks, good call on the blank