Hello

[wemsomba10]

I m using the switch function but the totals are coming out blank or just not doing any calculations. please help.

Selected In Report Switch =

var Myselection = SELECTEDVALUE('Change Type'[Type])

var selectNeg = IF([Selected In Report] < [Compared In Report],"Yes","No")

VAR SelectPos= IF([Selected In Report]> [Compared In Report],"Yes","No")

VAR SelectNo= IF([Selected In Report] = [Compared In Report],"Yes","No")

VAR SelectNew= IF(AND([Selected In Report]>0,[Compared In Report]=0),"Yes","No")

VAR SelectAll= IF([Selected In Report]>=0,"Yes","NO")

RETURN

SWITCH(TRUE(),

Myselection = "Negative Change" && selectNeg = "Yes", [Selected In Report],

Myselection = "Positive Change" && selectpos = "Yes",[Selected In Report],

Myselection = "No Change" && SelectNo = "Yes",[Selected In Report],

Myselection = "New Accnt Added" && selectNew = "Yes",[Selected In Report],

Myselection = "All"&& SelectAll = "Yes",[Selected In Report]

)

[Poojara_D12]

Hi @wemsomba10 

Please try the dax code:

Selected In Report Switch =
VAR Myselection = SELECTEDVALUE('Change Type'[Type])

-- Define comparison variables
VAR selectNeg = IF([Selected In Report] < [Compared In Report], "Yes", "No")
VAR selectPos = IF([Selected In Report] > [Compared In Report], "Yes", "No")
VAR selectNo = IF([Selected In Report] = [Compared In Report], "Yes", "No")
VAR selectNew = IF(AND([Selected In Report] > 0, [Compared In Report] = 0), "Yes", "No")
VAR selectAll = IF([Selected In Report] >= 0, "Yes", "No")

-- Handle the SWITCH logic
RETURN
SWITCH(TRUE(),
    Myselection = "Negative Change" && selectNeg = "Yes", [Selected In Report],
    Myselection = "Positive Change" && selectPos = "Yes", [Selected In Report],
    Myselection = "No Change" && selectNo = "Yes", [Selected In Report],
    Myselection = "New Accnt Added" && selectNew = "Yes", [Selected In Report],
    Myselection = "All" && selectAll = "Yes", [Selected In Report],
    
    -- Handle Total (default case for totals, using ISFILTERED to check if there's context)
    ISFILTERED('Change Type'[Type]), BLANK()  -- Return blank for the total row if no context
)

Try this modified version of the measure and let me know if the totals issue is resolved.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[wemsomba10]

Hello Poojara. this did not work. i am still getting either blank totals or the overall total. not giving totals when switching to position change, negative change, new accnt added and no change.

[Sahir_Maharaj]

Hello @wemsomba10,

Can you please try this approach:

Selected In Report Switch =
VAR Myselection = SELECTEDVALUE('Change Type'[Type], "All")
VAR Mth =
    CALCULATE(
        SUM(lowes_spend_data[In Report]),
        lowes_spend_data[Report Month Select Name] = SELECTEDVALUE('Selected Time Period'[Month Year])
    )
VAR Qtr =
    CALCULATE(
        SUM(lowes_spend_data[In Report]),
        lowes_spend_data[Report Quarter Select Name] = SELECTEDVALUE('Selected Time Period'[Quarter Year])
    )
VAR Yr =
    CALCULATE(
        SUM(lowes_spend_data[In Report]),
        lowes_spend_data[Report Year] = SELECTEDVALUE('Selected Time Period'[Report Year])
    )
VAR SelectedInReport =
    IF(
        ISFILTERED('Selected Time Period'[Month Year]),
        Mth,
        IF(
            ISFILTERED('Selected Time Period'[Quarter Year]),
            Qtr,
            IF(
                ISFILTERED('Selected Time Period'[Report Year]),
                Yr,
                0
            )
        )
    )
VAR selectNeg = IF(SelectedInReport < [Compared In Report], "Yes", "No")
VAR selectPos = IF(SelectedInReport > [Compared In Report], "Yes", "No")
VAR selectNo = IF(SelectedInReport = [Compared In Report], "Yes", "No")
VAR selectNew = IF(AND(SelectedInReport > 0, [Compared In Report] = 0), "Yes", "No")
VAR selectAll = IF(SelectedInReport >= 0, "Yes", "No")
VAR Result =
    SWITCH(
        TRUE(),
        Myselection = "Negative Change" && selectNeg = "Yes", SelectedInReport,
        Myselection = "Positive Change" && selectPos = "Yes", SelectedInReport,
        Myselection = "No Change" && selectNo = "Yes", SelectedInReport,
        Myselection = "New Accnt Added" && selectNew = "Yes", SelectedInReport,
        Myselection = "All" && selectAll = "Yes", SelectedInReport,
        BLANK()
    )
RETURN
IF(
    ISINSCOPE('Change Type'[Type]),
    Result,
    SUMX(
        VALUES('Change Type'[Type]),
        SWITCH(
            TRUE(),
            Myselection = "Negative Change", IF(selectNeg = "Yes", SelectedInReport, BLANK()),
            Myselection = "Positive Change", IF(selectPos = "Yes", SelectedInReport, BLANK()),
            Myselection = "No Change", IF(selectNo = "Yes", SelectedInReport, BLANK()),
            Myselection = "New Accnt Added", IF(selectNew = "Yes", SelectedInReport, BLANK()),
            Myselection = "All", IF(selectAll = "Yes", SelectedInReport, BLANK()),
            BLANK()
        )
    )
)

[wemsomba10]

thank you but the totals are still blank

[Anonymous]

Hi @wemsomba10 ,

I create a table as your DAX codes mentioned.

I think the reason for not displaying the results is the string of DAX code 'var Myselection = SELECTEDVALUE('Change Type'[Type])'. If when I use other functions I am able to display the results.

Selected In Report Switch = 
VAR _Myselection =
    MAX('Change Type'[Type] )
VAR _SelectNeg =
    IF ( [Selected In Report] < [Compared In Report], "Yes", "No" )
VAR _SelectPos =
    IF ( [Selected In Report] > [Compared In Report], "Yes", "No" )
VAR _SelectNo =
    IF ( [Selected In Report] = [Compared In Report], "Yes", "No" )
VAR _SelectNew =
    IF ( AND ( [Selected In Report] > 0, [Compared In Report] = 0 ), "Yes", "No" )
VAR _SelectAll =
    IF ( [Selected In Report] >= 0, "Yes", "NO" )
RETURN

    SWITCH (
        TRUE (),
        _Myselection = "Negative Change"
            && _selectNeg = "Yes", [Selected In Report],
        _Myselection = "Positive Change"
            && _selectpos = "Yes", [Selected In Report],
        _Myselection = "No Change"
            && _SelectNo = "Yes", [Selected In Report],
        _Myselection = "New Accnt Added"
            && _SelectNew = "Yes", [Selected In Report],
        _Myselection = "All"
            && _SelectAll = "Yes", [Selected In Report]
    )

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wemsomba10]

Hello,

This works but I am sorry. I forgot to mention this. the returned data " Selected In Report Switch" is also a switch function itsefl. here is the function for it. can you help please.

Selected In Report = IF(ISBLANK(

 VAR Mth =

    CALCULATE (

    sum(lowes_spend_data[In Report]),lowes_spend_data[Report Month Select Name]=SELECTEDVALUE('Selected Time Period'[Month Year]))

 VAR Qtr =

    CALCULATE (

    sum(lowes_spend_data[In Report]),lowes_spend_data[Report Quarter Select Name]=SELECTEDVALUE('Selected Time Period'[Quarter Year]))

VAR Yr =

    CALCULATE (

    sum(lowes_spend_data[In Report]),lowes_spend_data[Report Year]=SELECTEDVALUE('Selected Time Period'[Report Year]))

RETURN

IF(ISFILTERED('Selected Time Period'[Month Year]),Mth,

IF(ISFILTERED('Selected Time Period'[Quarter Year]),Qtr,

IF(ISFILTERED('Selected Time Period'[Report Year]),Yr))

)),0,

--else

VAR Mth =

    CALCULATE (

    sum(lowes_spend_data[In Report]),lowes_spend_data[Report Month Select Name]=SELECTEDVALUE('Selected Time Period'[Month Year]))

 VAR Qtr =

    CALCULATE (

    sum(lowes_spend_data[In Report]),lowes_spend_data[Report Quarter Select Name]=SELECTEDVALUE('Selected Time Period'[Quarter Year]))

VAR Yr =

    CALCULATE (

    sum(lowes_spend_data[In Report]),lowes_spend_data[Report Year]=SELECTEDVALUE('Selected Time Period'[Report Year]))

RETURN

IF(ISFILTERED('Selected Time Period'[Month Year]),Mth,

IF(ISFILTERED('Selected Time Period'[Quarter Year]),Qtr,

IF(ISFILTERED('Selected Time Period'[Report Year]),Yr))

))

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...