Create a distinctcount that count just the ID outside my date range

[Anonymous]

Hello to Everyone

I have two big problems for create a 2 different measures. The first one. I want calculate the distinct  IDUser  that are not prensent in my date range. 

Here my data model 

The join is   Asset[IDassegnatario] with Soggetto[IDPersona] 

I want obtain the  number of  distinct subjet that are not present inside the date range that i selected in the slicer. So if a subject compare inside the date range have to be exluded from the distinctcount.

I tried this dax code :

CALCULATE(distinctCOUNT(Dwh_D_Soggetto[IdPersona]),

FILTER ( Dwh_F_Asset, not(

Dwh_F_Asset[DataInizioValidita]<= CALCULATE(MAX(Dwh_D_Tempo[Data])) &&

Dwh_F_Asset[DataFineValidita] >= CALCULATE(MIN(Dwh_D_Tempo[Data])))))

But with this  formula if one of the subject >> [IDPersona] , is present outside my date range condition will be count one time by my starting formula distinctcount. 

So how i can wrote a DAX formula where i distinctcount the subject outside my date range and not included insiede my date Range  ? I deal with it since pass week and i can't find a properly solution

Thank you so much for the help

I 

[bcdobbs]

Hi sorry I posted earlier and then realised there was more complexity to the problem.

I think I've now fully understood and the following will help:

VAR LatestVisibleDate = MAX(Dwh_D_Tempo[Data]),
VAR EarliestVisibleDate = MIN(Dwh_D_Tempo[Data])

VAR IdsInDateRange = 
	TREATAS ( 
		CALCULATETABLE (
			VALUES ( Dwh_D_Asset[IDassegnatario] ), 
			Dwh_F_Asset[DataInizioValidita]<= LatestVisbleDate
			Dwh_F_Asset[DataFineValidita] >= EarliestVisibleDate
		),	
		Dwh_D_Soggetto[IDPersona] 
	)

VAR AllIds = 
	CALCULATETABLE (
		VALUES ( Dwh_D_Soggetto[IDPersona] ),
		REMOVEFILTERS ( Dwh_D_Soggetto )
	)

VAR IdsOutsideRange = 
	EXCEPT ( 
		AllIds, 
		IdsInDateRange 
	)

RETURN 
	COUNTROWS ( IdsOutsideRange )

[bcdobbs · Accepted solution]

IdsInDateRange gets a list of  IDassegnatario present inside the date range and uses TREATAS to move them back to the Soggetto dimension.

AllIds gets a list of all IDPersona. (Note if you removed the bidirectional filter the caclulate and removefilters wouldn't be needed).

IdsOutsideRange gets a list of all ids that are in AllIds but not in IdsInDateRange using EXCEPT.

It then just returns a count of these.

[Anonymous]

@bcdobbs  mate thank you so much really !!! It's really smooth your Dax formula i never saw and used the treatas formula, look like really massive. 

I will remove the bideriction in order to have just :

VAR AllIds =

        VALUES ( Dwh_D_Soggetto[IDPersona])

In this way i can filter directly from Dwh_D_Soggetto😀

[bcdobbs]

Perfect.

Glad it works. I think you can actually remove the TREATAS, thought I needed it to make the EXCEPT work but you don't.