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.
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")
Solved! Go to 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
)
)
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] ) )
)
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:
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
)
)
This works perfectly!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
69 | |
48 | |
44 | |
18 | |
15 |