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 ProductCode FirstRelease EndOfProduct PC_01 02/01/1990   PC_02 01/0...
  • 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

     

     

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