topic Re: Filtering data between two columns from different tables in DAX Commands and Tips

Filtering data between two columns from different tables

Jazzed2b — Fri, 14 Jun 2024 00:05:47 GMT

I have two tables with a ONE to MANY relationship via POLICY NUMBER. On the ONE table I have a column POLICY EFFECTIVE DATES and on the MANY table I have multiple drivers identified on a DELETEDATE. I want to filter to count only the number of drivers where the difference betwen POLICY EFFECTIVE DATES and DELETEDATE are less than 365. The reason being that when a policy expires after one year (365) they are all marked with a DELETEDATE 365 days after the POLICY EFFECTIVE DATE. I only want to cound the number of drivers which are deleted before the policy expires at 365.

Here is a sample of the table relationships. I have tried filtering the data based on the relationships, but I do not know if Power BI can subtract the DELETEDATE from the POLICY EFFECTIVE DATE as a condition prior to filter and summing the remaining driver. Is this something better accomplished as a query on Access DB and then loading the data in Power BI or is there a formula in Power BI to compare the dates on these two columns and then summing the drivers which qualify (meaning having a DELETEDATE less than 365 days from the POLICY EFFECTIVE DATE)?

Re: Filtering data between two columns from different tables

amitchandak — Fri, 14 Jun 2024 03:16:56 GMT

Jazzed2b , as they have on to many relation

a new column in diver report table

= datediff(related('AL Policy Data'[Effective Date]), 'Diver Data'[deleted date], date)

refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

Re: Filtering data between two columns from different tables

Anonymous — Fri, 14 Jun 2024 06:44:29 GMT

Hi Jazzed2b ,

You can try it by following steps:

1.you can create a calculated column to compute the difference in days

DaysDifference = DATEDIFF(RELATED(AL Policy Data[EFFECTIVE DATE]), Driver Report[DELETEDATE], DAY)

2.you can easily filter this table to include only the rows where is less than 365 and then count the number of drivers. This can be done by creating a measure

DriversCount = COUNTROWS(FILTER(Driver Report,Driver Report[DaysDifference] < 365))

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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