topic Re: Hello in DAX Commands and Tips

Hello

wemsomba10 — Thu, 05 Dec 2024 14:24:06 GMT

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]

)

Re: Hello

lbendlin — Thu, 05 Dec 2024 20:34:54 GMT

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-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Hello

Anonymous — Fri, 06 Dec 2024 02:22:33 GMT

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.

Re: Hello

wemsomba10 — Mon, 09 Dec 2024 22:32:32 GMT

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

))

Re: Hello

Sahir_Maharaj — Tue, 10 Dec 2024 16:48:15 GMT

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

Re: Hello

wemsomba10 — Tue, 10 Dec 2024 16:58:24 GMT

thank you but the totals are still blank

Re: Hello

Poojara_D12 — Wed, 11 Dec 2024 05:42:37 GMT

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

Re: Hello

wemsomba10 — Wed, 11 Dec 2024 16:17:12 GMT

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.