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

     

4 Replies

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