topic Creating a count via lookup on another table in DAX Commands and Tips

Creating a count via lookup on another table

matthewwylie — Wed, 16 Mar 2022 16:44:39 GMT

I have a Power Bi report containing two tables

table 1 - Licences table

table 2 - a date table

I created this with:

Date table = CALENDAR(MIN(Licences[Start date]), MAX(Licences[Start date]))

I want to add a measure to the date table that shows how many licences were active on each date

i.e

count licence number (no. licences) from the licence table where

date table.date >= licences.start date

date table.date <= licences.expiry date

Re: Creating a count via lookup on another table

amitchandak — Wed, 16 Mar 2022 17:14:38 GMT

matthewwylie , seems like very similar to my HR blog

refer

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Re: Creating a count via lookup on another table

Anonymous — Wed, 06 Apr 2022 07:37:50 GMT

Hi matthewwylie ,

Please refer this measure.

measure = CALCULATE ( COUNT ( 'Licences'[licence number] ), FILTER ( ALLSELECTED ( 'Licences' ), 'licences'[start date] <= SELECTEDVALUE ( 'date'[date] ) && 'licences'[expiry date] >= SELECTEDVALUE ( 'date'[date] ) ) )

Best Regards,

Jay