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 perfectly. But I get the alternate result back when I choose two filters. Is there any DAX expression I can use to create combinations of filters and decide what the response needs to be?

My measure currently
VAR A = Calculate (DISTINCTCOUNT(TableA), Dim_A[SalesType]="ABC")

VAR B = Calculate (DISTINCTCOUNT(TableA), Dim_A[SalesType]="DEF")

VAR C = Calculate (DISTINCTCOUNT(TableA), Dim_A[SalesType]="GHI")

 

SWITCH (
        SELECTEDVALUE ( Dim_A[SalesType] ),
        "ABC", A,
        "DEF", B,
        "GHI", C,
        A+B+C)
 
Ideally, I would like to be able to create combinations where I say If chosen values are "ABC"&"DEF", give me A+B, "DEF"&"GHI", give me B+C, etc. 
"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
    )
    )

4 Replies

  • tamerj1's avatar
    tamerj1
    Community 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
        Community 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
        )
        )