topic Re: Create a distinctcount that count just the ID outside my date range in DAX Commands and Tips

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

Anonymous — Wed, 29 Dec 2021 21:37:38 GMT

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

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

bcdobbs — Wed, 29 Dec 2021 22:42:38 GMT

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 )

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

bcdobbs — Wed, 29 Dec 2021 22:46:34 GMT

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.

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

Anonymous — Thu, 30 Dec 2021 11:21:07 GMT

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😀

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

bcdobbs — Thu, 30 Dec 2021 11:52:31 GMT

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.