Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

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

View solution in original post

4 REPLIES 4
tamerj1
Super User
Super User

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
Not applicable

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? 

@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
Not applicable

This works perfectly!

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors