topic Dynamic distinctcount using a variable data in DAX Commands and Tips

Dynamic distinctcount using a variable data

anne-sophie — Mon, 18 Jan 2021 13:35:21 GMT

Hello community!

Here is an issue I keep thinking about without finding the solution...

I have 2 tables :

product

ProductCode	FirstRelease	EndOfProduct
PC_01	02/01/1990
PC_02	01/06/2017
PC_03	01/06/2018
PC_04	01/06/2019
PC_05	01/06/2020
PC_06	01/01/2021
PC_07	02/01/1990
PC_08	02/01/1990	01/03/2018
PC_09	02/01/1990	01/03/2019
PC_10	02/01/1990	01/03/2020
PC_11	02/01/1990	01/01/2021

and years:

Years

2018

2019

2020

2021

And I need to calculate how many products were live for each year (with a fixed date wihtin the year, for example Jan 1st) : for each row in Year table, calculate distinctcount(ProductCode) from Product table where FirstRelease <= 01/01/Year and (EndOfProduct is null or EndOfProduct > 01/01/Year).

I manage to do that in PowerQuery but I wonder if it's possible in DAX, as my product table has many many many rows and multiplying them by the number of years to evaluate is not the best. IRL, my ProductTable also have other columns which are used as filters in the report, that's why the counting needs to be dynamic.

Any help appreciated !

Re: Dynamic distinctcount using a variable data

DataInsights — Mon, 18 Jan 2021 15:30:46 GMT

anne-sophie,

Try this measure. No relationship exists between the two tables.

Distinct Product Count = VAR vYear = MAX ( Years[Year] ) VAR vStartDate = DATE ( vYear, 1, 1 ) VAR vProductRows = FILTER ( Products, vStartDate >= Products[FirstRelease] && ( vStartDate <= Products[EndOfProduct] || ISBLANK ( Products[EndOfProduct] ) ) ) VAR vResult = CALCULATE ( DISTINCTCOUNT ( Products[ProductCode] ), vProductRows ) RETURN vResult

Re: Dynamic distinctcount using a variable data

anne-sophie — Tue, 19 Jan 2021 07:26:09 GMT

Thank you so much ! It works, and I understand the measure's code, so I've improved my DAX skill 🙂

Re: Dynamic distinctcount using a variable data

DataInsights — Tue, 19 Jan 2021 15:40:08 GMT

anne-sophie,

Glad to hear that worked, and that your DAX skills improved. 🙂