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

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!