Forum Discussion

ShrutiJ's avatar
ShrutiJ
Icon for Helper II rankHelper II
3 years ago
Solved

Count Unique category based on Value and Date

Hi, I have data in the below format. I need a measure which will count distinct number of "Categories" which has atleast 2 "Product IDs" purchased in last 3 months. In the below example, I have 2 p...
Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, ShrutiJ 

     

    You can try the following methods.
    Measure:

    Count Product =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Product ID] ),
    FILTER (
        ALL ( 'Table' ),
        MONTH ( 'Table'[Purchase Date] )
            >= MONTH ( TODAY () ) - 3
            && [Category] = SELECTEDVALUE ( 'Table'[Category] )
    )
)


    Count Unique category = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Category] ),
    FILTER ( ALL ( 'Table' ), [Count Product] >= 2 )
)

    Is this the result you expect?

    Best Regards,

    Community Support Team _Charlotte

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

     

v-zhangti's avatar
v-zhangti
Icon for Community Support rankCommunity Support
3 years ago

Hi, ShrutiJ 

 

You can try the following methods.
Measure:

Count Product =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Product ID] ),
    FILTER (
        ALL ( 'Table' ),
        MONTH ( 'Table'[Purchase Date] )
            >= MONTH ( TODAY () ) - 3
            && [Category] = SELECTEDVALUE ( 'Table'[Category] )
    )
)


Count Unique category = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Category] ),
    FILTER ( ALL ( 'Table' ), [Count Product] >= 2 )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

 

  • ShrutiJ's avatar
    ShrutiJ
    Icon for Helper II rankHelper II
    3 years ago

    Thank you v-zhangti ! This is working perfectly!