Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Tricky Switch and AND Function - combination

Hi Experts   I am trying to get the following out come based on the slicer selection criteria.... 1. If the user selects Gross Margin (first slicer) and then Reported (second slicer) then return t...
Need Help
  • LaurentCouartou's avatar
    LaurentCouartou
    7 years ago

    SWITCH, just like IF, cannot return a table expression.

     

    Have you tried something like this?

    SWITCH(TRUE(),
    ProfitbabilityMeasure = "Gross Margin" && ReportingMeasure = "Reported"
       , CALCULATE( [GMvPY1% (Reported PY)]
            , TOPN(10, ALL('Product'[Category]),[GMvPY1% (Reported PY)])
    	)
    	, ...
    )

     

LaurentCouartou's avatar
LaurentCouartou
Solution Supplier
7 years ago

SWITCH, just like IF, cannot return a table expression.

 

Have you tried something like this?

SWITCH(TRUE(),
    ProfitbabilityMeasure = "Gross Margin" && ReportingMeasure = "Reported"
       , CALCULATE( [GMvPY1% (Reported PY)]
            , TOPN(10, ALL('Product'[Category]),[GMvPY1% (Reported PY)])
	)
	, ...
)

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks for the excellent feedback,,,

     

    I am guess we need to add RankingContext
     ))

     

    to th eend of the statement...

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi this is my full measure....cannot see my error...

      Top Products Reported:- Gross Margin / Net Sales = 
VAR RankingContext = VALUES('Product'[Category])
VAR ProfitbabilityMeasure = SELECTEDVALUE(ProfitabilityMeasures[MeasureName])
VAR ReportingMeasure = SELECTEDVALUE(Reporting[Reporting])
RETURN

SWITCH(TRUE(),
    ProfitbabilityMeasure = "Gross Margin" && ReportingMeasure = "Reported"
       , CALCULATE( [GMvPY1% (Reported PY)]
            , TOPN(10, ALL('Product'[Category]),[GMvPY1% (Reported PY)])),
            RankingContext,
            
    ProfitbabilityMeasure = "Gross Margin" && ReportingMeasure = "NOE"
       , CALCULATE( [GMvPY3% (NOE @ PY Rate)]
            , TOPN(10, ALL('Product'[Category]),[GMvPY3% (NOE @ PY Rate)]),
            RankingContext,
        
    ProfitbabilityMeasure = "Gross Margin" 
       , CALCULATE( [GMvPY1% (Reported PY)]
            , TOPN(10, ALL('Product'[Category]),[GMvPY1% (Reported PY)]),
            RankingContext)))
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      got it to work....thanks