Forum Discussion

anne-sophie's avatar
anne-sophie
Frequent Visitor
5 years ago
Solved

Dynamic distinctcount using a variable data

Hello community!

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

I have 2 tables :

product

ProductCodeFirstReleaseEndOfProduct
PC_0102/01/1990 
PC_0201/06/2017 
PC_0301/06/2018 
PC_0401/06/2019 
PC_0501/06/2020 
PC_0601/01/2021 
PC_0702/01/1990 
PC_0802/01/199001/03/2018
PC_0902/01/199001/03/2019
PC_1002/01/199001/03/2020
PC_1102/01/199001/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 !

  • DataInsights's avatar
    DataInsights
    5 years ago

    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

     

     

3 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    5 years ago

    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

     

     

  • anne-sophie's avatar
    anne-sophie
    Frequent Visitor
    5 years ago

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