topic Re: DAX calculate with filters from two different dimensions in DAX Commands and Tips

DAX calculate with filters from two different dimensions

Lannersten — Thu, 30 Jan 2020 22:50:24 GMT

Suppose that you want to count your customers

And that you just want to count those that has money in their account (This filter is easy and is applied in my code sample)

And sadly some of your customers pass away and you just want to count them as long as their alive (Scary ?)

In my sample I have tried to visualize what I am expecting.

My problem is in that I need to filter rows in my result based on if the customer is alive at this specific time or not.
The dateofdeath is in Dim_Customer, and present time is in Dim_Date, my filter would be something like Dim_Customer[DateOfDeath] > Dim_Date[PeriodDateTo] But this gives an error

SO how would you solve this problem?

Best Regards, Joakim

Re: DAX calculate with filters from two different dimensions

Anonymous — Fri, 31 Jan 2020 03:46:49 GMT

Lannersten Please try below measure and check if it works for you.

Measure = VAR _customerId = SELECTEDVALUE('Fact'[CustomerId]) VAR _deathdate = CALCULATE(MAX(DimCustomer[Deathdate]),DimCustomer[CustomerId]=_customerId) RETURN CALCULATE(DISTINCTCOUNT('Fact'[CustomerId]),'Fact'[Amount]>0,Dim_Date[To]<_deathdate)

Re: DAX calculate with filters from two different dimensions

Lannersten — Fri, 31 Jan 2020 17:11:38 GMT

Hi Vimal

I had an issue with the selectedvalue formula in Excel, but I retyped it like this:

NumOfCustomersAlive:=VAR _customerId = IF(HASONEVALUE('Facts'[CustomerId]);VALUES('Facts'[CustomerId]))
VAR _deathdate = CALCULATE(MAX(Dim_Customer[CalculatedDateOfDeath]);Dim_Customer[CustomerId]=_customerId)
RETURN CALCULATE(DISTINCTCOUNT('Facts'[CustomerId]);'Facts'[Amount]>0;Dim_Date[PeriodDatumTo]<_deathdate)

And it made the job, but when I added one more CustomerId to my facts table, the code fails. I guess it has to do with VAR _customerid just keeps one value ? Or? (In my production environment I have a couple of millions of customers...)

I have attached the result and my expected result + The data I'm using.

Do you suggest some other tweak to my code?

Best, Joakim