topic Re: switch() and SelectedValue for multiple filter choices in DAX Commands and Tips

switch() and SelectedValue for multiple filter choices

Anonymous — Mon, 01 May 2023 13:02:29 GMT

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.

Re: switch() and SelectedValue for multiple filter choices

tamerj1 — Mon, 01 May 2023 14:31:49 GMT

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

Re: switch() and SelectedValue for multiple filter choices

Anonymous — Mon, 01 May 2023 15:04:00 GMT

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?

Re: switch() and SelectedValue for multiple filter choices

tamerj1 — Mon, 01 May 2023 15:19:39 GMT

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

Re: switch() and SelectedValue for multiple filter choices

Anonymous — Tue, 02 May 2023 07:40:22 GMT

This works perfectly!