Switch DAX formula error - Tricky

[Anonymous]

Hi Experts

Cann see the wood for the tree as to what i am doing wrong with the following switch when using variables...

% Reported Central 2 = 
VAR ReportingMeasure = SELECTEDVALUE(Reporting[Reporting])
VAR InorganicMeasure = SELECTEDVALUE(InOrganic[InOrganic])

VAR NSales =
        SWITCH(
    ReportingMeasure, "Reported", [NSvPY1% (Reported PY)],BLANK()) 
VAR NSales1 = 
        SWITCH(
    ReportingMeasure, "NOE",[NSvPY3% (NOE @ PY Rate)], BLANK())        
VAR Organic = 
        SWITCH(
    ReportingMeasure, "Organic",[% Organic Growth Movement], BLANK())    
Return

SWITCH(NSales, "Reported", NSales1, "NOE", Organic, "Organic",BLANK()) 

[OwenAuger · Accepted solution]

Hi @Anonymous

From the code you've posted, the SWITCH function call on the very last line looks strange.

Specifically, the final SWITCH's intended first argument seems to be missing, and the other pairs of arguments seem to be the wrong way around.

I'm guessing a bit, but did you intend:

SWITCH ( ______, "Reported", NSales, "NOE", NSales1, "Organic", Organic, BLANK() )

Also you haven't used the InorganicMeasure variable after defining it. Not sure if that is supposed to be the first argument of SWITCH here.

Regards,

Owen

[OwenAuger · Accepted solution]

Hi @Anonymous

From the code you've posted, the SWITCH function call on the very last line looks strange.

Specifically, the final SWITCH's intended first argument seems to be missing, and the other pairs of arguments seem to be the wrong way around.

I'm guessing a bit, but did you intend:

SWITCH ( ______, "Reported", NSales, "NOE", NSales1, "Organic", Organic, BLANK() )

Also you haven't used the InorganicMeasure variable after defining it. Not sure if that is supposed to be the first argument of SWITCH here.

Regards,

Owen

[Anonymous]

thanks owen - i see the error...much appreciated

[Anonymous]

Hi Expets

Need some help as i cannot still see the error here on the switch function...

Should read as follows: if from reporting measure either NOE or Reported are selected then return below, then if niether NOE or Reported are selected then Inorganic

% Reported Central 2 = 
VAR ReportingMeasure = SELECTEDVALUE(Reporting[Reporting])
VAR InorganicMeasure = SELECTEDVALUE(InOrganic[InOrganic])

VAR NSales =
        SWITCH(
    ReportingMeasure, "Reported", [NSvPY1% (Reported PY)],BLANK())
VAR NSales1 =    
        SWITCH(
    ReportingMeasure, "NOE",[NSvPY3% (NOE @ PY Rate)], BLANK())  
Var Org1 =         
        SWITCH(       
    InorganicMeasure, "Organic",[% Organic Growth Movement],BLANK())        

Return

SWITCH(ReportingMeasure, "Reported", NSales, "NOE", NSales1, Org1,BLANK()) 

[Greg_Deckler · Accepted solution]

Sample data would help tremendously. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

That being said, what error are you getting? Is this a column or measure?

The way you have it structured, I believe you want this instead:

% Reported Central 2 = 
VAR ReportingMeasure = SELECTEDVALUE(Reporting[Reporting])
VAR InorganicMeasure = SELECTEDVALUE(InOrganic[InOrganic])

VAR NSales =
        SWITCH(
		ReportingMeasure, 
		"Reported", [NSvPY1% (Reported PY)],
		BLANK()
	)

VAR NSales1 =    
        SWITCH(
    		ReportingMeasure,
		"NOE",[NSvPY3% (NOE @ PY Rate)], 
		BLANK()
	)  

Var Org1 =         
        SWITCH(       
    		InorganicMeasure, 
		"Organic",[% Organic Growth Movement],
		BLANK())        

Return

SWITCH(
	ReportingMeasure, 
	"Reported",NSales,
	"NOE",NSales1, 
	Org1
)

But this seems like it could be way simplified:

% Reported Central 2 = 
VAR ReportingMeasure = SELECTEDVALUE(Reporting[Reporting])
VAR InorganicMeasure = SELECTEDVALUE(InOrganic[InOrganic])

Var Org1 =         
        SWITCH(       
    		InorganicMeasure, 
		"Organic",[% Organic Growth Movement],
		BLANK())        

RETURN
SWITCH(
	ReportingMeasure,
	"Reported",[NSvPY1% (Reported PY)],
	"NOE",[NSvPY3% (NOE @ PY Rate)],
	Org1
)

[Anonymous]

thanks Greg i see the error. much appericated as always. excellent feedback