Distinct count IDs from query, where IDs NOT in other query

[GuldagerHerDK]

Hi.

I'm having trouble with counting a certain amount of IDs based on a date input.  It works great when i run one query at a time. 

I want to exclude all the ids from VAR DoNotWithtake when counting the id's from VAR withtake

heres my DAX. Do anyone have an idea to what i'm doing wrong?. 

Measure =

VAR SAEndDatePlusOneMonth =

FIRSTDATE ( DATEADD ( Dato[Dato], 1, MONTH ) )

VAR SAStartDatePlusTwoMonths =

FIRSTDATE ( DATEADD ( Dato[Dato], 2, MONTH ) )

VAR DoNotWithtake =

CALCULATE (

DISTINCTCOUNT ( 'Målerpunkt'[Metering Point ID] ),

FILTER (

CALCULATETABLE ( VALUES ( FactSA ), ALL ( 'Dato' ) ),

FactSA[SAStartDate] >= CALCULATE ( MIN ( Dato[Dato] ) )

&& FactSA[SAStartDate] < SAStartDatePlusTwoMonths

&& FactSA[SAEndDate] >= SAEndDatePlusOneMonth

),

FILTER ( SA, NOT ( SA[SA status] IN { "Canceled", "Pending Start" } ) ),

ALL ( Dato[Dato] )

)

VAR withtake =

CALCULATE (

DISTINCTCOUNT( 'Målerpunkt'[Metering Point ID] ),

FILTER (

CALCULATETABLE ( VALUES ( FactSA[SAEndDate] ), ALL ( 'Dato' ) ),

FactSA[SAEndDate] >= CALCULATE ( MIN ( Dato[Dato] ) )

&& FactSA[SAEndDate] < SAEndDatePlusOneMonth

),

FILTER ( SA, SA[SA status] IN { "Stopped", "Closed" } ),

ALL ( Dato[Dato] )

)

return

CALCULATE(withtake,FILTER('Målerpunkt',not('Målerpunkt'[Metering Point ID] in {DoNotWithtake})))

There is typicyally two errors i stump upon. 

1. A table of multiple values was supplied where a single value was expected
2. Function 'CONTAINSROW' does not support comparing values of type integer with values of type text.

Any ideas or tips will be much appreciated

[v-yanjiang-msft · Accepted solution]

Hi @GuldagerHerDK ,

In your formula bellow, “DoNotWithtake” returns a number of count, while “MeteringPointID” is the content of ID, they don’t have an inclusive relationship.

You can modify “DoNotWithtake” like this:

VAR _Table=

FILTER (

CALCULATETABLE ( VALUES ( FactSA ), ALL ( 'Dato' ) ),

FactSA[SAStartDate] >= CALCULATE ( MIN ( Dato[Dato] ) )

&& FactSA[SAStartDate] < SAStartDatePlusTwoMonths

&& FactSA[SAEndDate] >= SAEndDatePlusOneMonth

),

FILTER ( SA, NOT ( SA[SA status] IN { "Canceled", "Pending Start" } )

)

VAR _DoNotWithtake =

SELECTCOLUMNS(_Table, “ Metering Point ID”, [Metering Point ID])

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.

[GuldagerHerDK]

Hi @lbendlin 🙂

What i want to is count the number of id's from one query (QUERY 1) which are NOT IN in other query, based on query 2s filters.

I have the following two queries.

Query 1: 
 CALCULATE (
DISTINCTCOUNT('Målerpunkt'[Metering Point ID] ),
FILTER (
CALCULATETABLE ( VALUES ( FactSA[SAEndDate] ), ALL ( 'Dato' ) ),
FactSA[SAEndDate] >= CALCULATE ( MIN ( Dato[Dato] ) )
&& FactSA[SAEndDate] < SAEndDatePlusOneMonth
),
FILTER ( SA, SA[SA status] IN { "Stopped", "Closed" } ),
ALL ( Dato[Dato] )
)


Query 2:

CALCULATE (
VALUES('Målerpunkt'[Metering Point ID] ),
FILTER (
CALCULATETABLE ( VALUES ( FactSA ), ALL ( 'Dato' ) ),
FactSA[SAStartDate] >= CALCULATE ( MIN ( Dato[Dato] ) )
&& FactSA[SAStartDate] < SAStartDatePlusTwoMonths
&& FactSA[SAEndDate] >= SAEndDatePlusOneMonth
),
FILTER ( SA, NOT ( SA[SA status] IN { "Canceled", "Pending Start" } ) ),
ALL ( Dato[Dato] )
)

in TSQL i would do something like this:

SELECT '2021-09-01' AS PrimoDato,
AccountBrand,
a.AccountCustomerType,
COUNT(DISTINCT ServicePointMeteringPoint) AS AntalMPChurn
FROM [ElsalgDW].[fact].[SAs] sa
LEFT JOIN ElsalgDW.dim.SA dsa ON sa.SAID = dsa.SAID
LEFT JOIN ElsalgDW.dim.ServicePoint sp ON sa.ServicePointID = sp.ServicePointID
LEFT JOIN ElsalgDW.dim.Account a ON sa.AccountID = a.AccountID
WHERE SAEndDate >= '2021-09-01'
AND SAEndDate < DATEADD(mm, 1, '2021-09-01')
AND ServicePointMeteringPoint NOT IN
(
SELECT DISTINCT
ServicePointMeteringPoint
FROM [ElsalgDW].[fact].[SAs] sa
LEFT JOIN ElsalgDW.dim.SA dsa ON sa.SAID = dsa.SAID
LEFT JOIN ElsalgDW.dim.ServicePoint sp ON sa.ServicePointID = sp.ServicePointID
LEFT JOIN ElsalgDW.dim.Account a ON sa.AccountID = a.AccountID
WHERE(SAstartDate >= '2021-09-01'
AND SAstartDate < DATEADD(mm, 2, '2021-09-01'))
AND SAEndDate >= DATEADD(mm, 1, '2021-09-01')
AND SAStatusName NOT IN('Canceled', 'Pending Start')
)
AND SAStatusName IN('Stopped', 'Closed')
GROUP BY a.AccountBrand,
a.AccountCustomerType;



which would output this.