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)])
    	)
    	, ...
    )

     

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