Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

switch() and SelectedValue for multiple filter choices

I am currently trying to build a measure that has three variables. To return the values, I used Switch()  to allow for the selection of filters. When I use distinctive values, the measure works perfe...
"SELECTEDVALUE"
"VAR"
  • tamerj1's avatar
    tamerj1
    3 years ago

    Anonymous 

    =
    VAR NumberoppsRenewals =
    CALCULATE (
    'Calculations'[Number of Opportunities (Historical)],
    AND (
    Fact_OpportunitiesHistorical[StageName] <> "Closed Won",
    Fact_OpportunitiesHistorical[StageName] <> "Closed Lost"
    ),
    Dim_SalesType[SalesType] = "Renewal"
    )
    VAR NumberoppsExpansion =
    CALCULATE (
    'Calculations'[Number of Opportunities (Historical)],
    AND (
    Fact_OpportunitiesHistorical[StageName] <> "Closed Won",
    Fact_OpportunitiesHistorical[StageName] <> "Closed Lost"
    ),
    Fact_OpportunitiesHistorical[SolutionFitStageDate] <> BLANK (),
    Dim_SalesType[SalesType] = "Existing Customer"
    )
    VAR NumberoppsNewBiz =
    CALCULATE (
    'Calculations'[Number of Opportunities (Historical)],
    AND (
    Fact_OpportunitiesHistorical[StageName] <> "Closed Won",
    Fact_OpportunitiesHistorical[StageName] <> "Closed Lost"
    ),
    Fact_OpportunitiesHistorical[SolutionFitStageDate] <> BLANK (),
    Dim_SalesType[SalesType] = "New Customer"
    )
    RETURN
    SUMX (
    VALUES ( Dim_SalesType[SalesType] ),
    SWITCH (
    Dim_SalesType[SalesType],
    "New Customer", NumberoppsNewBiz,
    "Existing Customer", NumberoppsExpansion,
    "Renewal", NumberoppsRenewals
    )
    )

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi Anonymous 
Seems to me you just want to force additivity ove a DISTINCTCOUNT measure. Please try

MyMeasure =
SUMX (
    VALUES ( Dim_A[SalesType] ),
    CALCULATE ( DISTINCTCOUNT ( TableA[Column] ) )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi tamerj1 , thanks for the response. I gave the measure as a very simplified version, but if you wish to see the whole measure, please find it below:

    VAR NumberoppsRenewals =
        CALCULATE (
            'Calculations'[Number of Opportunities (Historical)],
            AND (
                Fact_OpportunitiesHistorical[StageName] <> "Closed Won",
                Fact_OpportunitiesHistorical[StageName] <> "Closed Lost"
            ),
            Dim_SalesType[SalesType] = "Renewal"
        )
    VAR NumberoppsExpansion =
        CALCULATE (
            'Calculations'[Number of Opportunities (Historical)],
            AND (
                Fact_OpportunitiesHistorical[StageName] <> "Closed Won",
                Fact_OpportunitiesHistorical[StageName] <> "Closed Lost"
            ),
            Fact_OpportunitiesHistorical[SolutionFitStageDate] <> BLANK (),
            Dim_SalesType[SalesType] = "Existing Customer"
        )
    VAR NumberoppsNewBiz =
        CALCULATE (
            'Calculations'[Number of Opportunities (Historical)],
            AND (
                Fact_OpportunitiesHistorical[StageName] <> "Closed Won",
                Fact_OpportunitiesHistorical[StageName] <> "Closed Lost"
            ),
            Fact_OpportunitiesHistorical[SolutionFitStageDate] <> BLANK (),
            Dim_SalesType[SalesType] = "New Customer"
        )
    RETURN
        SWITCH (
            SELECTEDVALUE ( Dim_SalesType[SalesType] ),
            "New Customer", NumberoppsNewBiz,
            "Existing Customer", NumberoppsExpansion,
            "Renewal", NumberoppsRenewals,
            NumberoppsNewBiz+NumberoppsExpansion+NumberoppsRenewals
        )


    Because I have so many additional filters, I used switch() to be able to toggle between the variables. Do you think your recommended measure will still work? 

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      3 years ago

      Anonymous 

      =
      VAR NumberoppsRenewals =
      CALCULATE (
      'Calculations'[Number of Opportunities (Historical)],
      AND (
      Fact_OpportunitiesHistorical[StageName] <> "Closed Won",
      Fact_OpportunitiesHistorical[StageName] <> "Closed Lost"
      ),
      Dim_SalesType[SalesType] = "Renewal"
      )
      VAR NumberoppsExpansion =
      CALCULATE (
      'Calculations'[Number of Opportunities (Historical)],
      AND (
      Fact_OpportunitiesHistorical[StageName] <> "Closed Won",
      Fact_OpportunitiesHistorical[StageName] <> "Closed Lost"
      ),
      Fact_OpportunitiesHistorical[SolutionFitStageDate] <> BLANK (),
      Dim_SalesType[SalesType] = "Existing Customer"
      )
      VAR NumberoppsNewBiz =
      CALCULATE (
      'Calculations'[Number of Opportunities (Historical)],
      AND (
      Fact_OpportunitiesHistorical[StageName] <> "Closed Won",
      Fact_OpportunitiesHistorical[StageName] <> "Closed Lost"
      ),
      Fact_OpportunitiesHistorical[SolutionFitStageDate] <> BLANK (),
      Dim_SalesType[SalesType] = "New Customer"
      )
      RETURN
      SUMX (
      VALUES ( Dim_SalesType[SalesType] ),
      SWITCH (
      Dim_SalesType[SalesType],
      "New Customer", NumberoppsNewBiz,
      "Existing Customer", NumberoppsExpansion,
      "Renewal", NumberoppsRenewals
      )
      )

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        This works perfectly!