Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Switch DAX formula error - Tricky

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[...
Need Help
  • OwenAuger's avatar
    OwenAuger
    7 years ago

    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

  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

    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
)
OwenAuger's avatar
OwenAuger
Super User
7 years ago

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

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Greg_Deckler
        Community Champion
        7 years ago

        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
)