Forum Discussion
Filter looking at 2 measures not working.
Thanks for the pbix. Try this measure:
Distinct Count Combined = VAR vRunningTotal = CALCULATETABLE ( ADDCOLUMNS ( VALUES ( 'TEST CHARGES'[BI_ACCT] ), "@CumulativeHBO", [Cummulative HBO], "@CumulativeTotalStream", [Cummulative Total Stream] ), 'Test Charges Date Table'[Date] <= MAX ( 'Test Charges Date Table'[Date] ) ) VAR vActiveCustomers = SELECTCOLUMNS ( FILTER ( vRunningTotal, [@CumulativeHBO] = 1 && [@CumulativeTotalStream] = 1 ), "BI_ACCT", 'TEST CHARGES'[BI_ACCT] ) VAR vResult = CALCULATE ( DISTINCTCOUNT ( 'TEST CHARGES'[BI_ACCT] ), vActiveCustomers ) RETURN vResult
Super User
Try this measure:
Distinct Count Combined =
VAR vHBO =
CALCULATETABLE (
VALUES ( 'TEST CHARGES'[BI_ACCT] ),
'Test Charges Date Table'[Date] <= MAX ( 'Test Charges Date Table'[Date] ),
'TEST CHARGES'[CHG_DESC] = "HBO"
)
VAR vStream =
CALCULATETABLE (
VALUES ( 'TEST CHARGES'[BI_ACCT] ),
'Test Charges Date Table'[Date] <= MAX ( 'Test Charges Date Table'[Date] ),
'TEST CHARGES'[CHG_DESC] IN { "Streaming Local Plus", "Streaming Complete" }
)
VAR vBoth =
INTERSECT ( vHBO, vStream )
VAR vResult =
CALCULATE ( DISTINCTCOUNT ( 'TEST CHARGES'[BI_ACCT] ), vBoth )
RETURN
vResult
DataInsights
Thank you for the quick reply. This is very close but currently brings in any customer whos ever had both services. In my original code the "Sum of Running Total" checks to see if a particular service is active or not.
It's just adding up 1s and -1s where 1 equals active and 0 equals inactive. I filtered each measure to only look at specific charge codes so that it would see if those specific services were active. If no service is designated it just checks if the account itself is active.
Sum of Running Total =
SUM('TEST CHARGES'[RUNNING_TOTAL_VALUE])
Can I add that calculation to the calculatate table variables?
- DataInsights
Try this:
Distinct Count Combined = VAR vHBO = CALCULATETABLE ( VALUES ( 'TEST CHARGES'[BI_ACCT] ), 'Test Charges Date Table'[Date] <= MAX ( 'Test Charges Date Table'[Date] ), 'TEST CHARGES'[CHG_DESC] = "HBO", 'TEST CHARGES'[RUNNING_TOTAL_VALUE] = 1 ) VAR vStream = CALCULATETABLE ( VALUES ( 'TEST CHARGES'[BI_ACCT] ), 'Test Charges Date Table'[Date] <= MAX ( 'Test Charges Date Table'[Date] ), 'TEST CHARGES'[CHG_DESC] IN { "Streaming Local Plus", "Streaming Complete" }, 'TEST CHARGES'[RUNNING_TOTAL_VALUE] = 1 ) VAR vBoth = INTERSECT ( vHBO, vStream ) VAR vResult = CALCULATE ( DISTINCTCOUNT ( 'TEST CHARGES'[BI_ACCT] ), vBoth ) RETURN vResultI'm hoping this picture helps illustrate the running total value. If we look at
"Running Total Value" = 1 then we still have every account that EVER had service since they all "START" with a 1. In the example we see on Dec 2nd they stopped service and the "Sum of Running Total" is now 0. They can start and stop service as many times as they'd like so we have to always look at the sum of the running totals.
When I try adding the measure to the filter condition I get the placeholder error code.VAR vHBO = CALCULATETABLE ( VALUES ( 'TEST CHARGES'[BI_ACCT] ), 'Test Charges Date Table'[Date] <= MAX ( 'Test Charges Date Table'[Date] ), [Sum of Running Total] = 1, 'TEST CHARGES'[CHG_DESC] = "HBO" )If we're able to get it to accept the measure I beleive we'd have the finished product.
- DataInsights
Try this. Would you be able to provide sample data (paste as table), and a screenshot of the expected result? This will facilitate testing.
Distinct Count Combined = VAR vRunningTotal = CALCULATETABLE ( ADDCOLUMNS ( SUMMARIZE ( 'TEST CHARGES', 'TEST CHARGES'[BI_ACCT], 'TEST CHARGES'[CHG_DESC] ), "@RunningTotal", [Sum of Running Total] ), 'Test Charges Date Table'[Date] <= MAX ( 'Test Charges Date Table'[Date] ) ) VAR vActiveCustomers = SELECTCOLUMNS ( FILTER ( vRunningTotal, [@RunningTotal] = 0 ), "BI_ACCT", 'TEST CHARGES'[BI_ACCT] ) VAR vHBO = CALCULATETABLE ( VALUES ( 'TEST CHARGES'[BI_ACCT] ), 'Test Charges Date Table'[Date] <= MAX ( 'Test Charges Date Table'[Date] ), 'TEST CHARGES'[CHG_DESC] = "HBO", vActiveCustomers ) VAR vStream = CALCULATETABLE ( VALUES ( 'TEST CHARGES'[BI_ACCT] ), 'Test Charges Date Table'[Date] <= MAX ( 'Test Charges Date Table'[Date] ), 'TEST CHARGES'[CHG_DESC] IN { "Streaming Local Plus", "Streaming Complete" }, vActiveCustomers ) VAR vBoth = INTERSECT ( vHBO, vStream ) VAR vResult = CALCULATE ( DISTINCTCOUNT ( 'TEST CHARGES'[BI_ACCT] ), vBoth ) RETURN vResult