switch() and SelectedValue for multiple filter choices

[Anonymous]

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. 

[tamerj1]

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]

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 · Accepted solution]

@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]

This works perfectly!