Tricky Switch and AND Function - combination

[Anonymous]

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 the Top 10 Products based on the Reported measure...

2. If the user selects Gross Margin (first slicer) and then NOE (second slicer) then return the Top 10 Products based on the NOE measure...

else

3. If the user selects Gross Margin (first slicer) and nothing on second slicer then return the Top 10 Products based on the Gross Margin measure.....

Cannot get the formula to work....

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

CALCULATE(SWITCH(TRUE(),AND(
    ProfitbabilityMeasure = "Gross Margin",
    ReportingMeasure = "Reported"), [GMvPY1% (Reported PY)],
    TOPN(10, ALL('Product'[Category]),[GMvPY1% (Reported PY)]),
                        AND(
    ProfitbabilityMeasure = "Gross Margin",
    ReportingMeasure = "NOE"), [GMvPY3% (NOE @ PY Rate)],
    TOPN(10, ALL('Product'[Category]),[GMvPY3% (NOE @ PY Rate)]),
    
    ProfitbabilityMeasure = "Gross Margin",[GMvPY1% (Reported PY)],
    TOPN(10, ALL('Product'[Category]),[GMvPY1% (Reported PY)]),
    RankingContext,BLANK(),0)
   )

[LaurentCouartou · Accepted solution]

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]

Thanks for the excellent feedback,,,

I am guess we need to add RankingContext
 ))

to th eend of the statement...

[Anonymous]

got it to work....thanks

[Anonymous]

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