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 product IDs purchased in Nov for Category A and 3 product IDs purchased in Oct for Category C. Records marked in Blue.

Current month is Nov, both Oct and Nov fall in last 3 months. Category C has no product purchased in last 3 months.

So I need a meaure which will retun the unique Category count, that is, 2.

Could someone please help with this?

To add, I'm using tabular model. Hence, can't create a new column. I can only create a DAX measure.

 

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.

     

4 Replies

  • 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.

     

  • Shaurya's avatar
    Shaurya
    Icon for Memorable Member rankMemorable Member
    3 years ago

    Hi ShrutiJ,

     

    Use this formula to create a flag first, that marks the Categories with atleast two Product IDs in the last 3 months:

     

    Flag = IF(COUNTX(FILTER('Table',DATEDIFF('Table'[Purchase Date],TODAY(),MONTH)<=3),IF('Table'[Category]=EARLIER('Table'[Category]),'Table'[Product ID]))>=2,1,0)

     

    Then create this measure to count the distinct categories based on that flag and add it in a card:

     

    Count = CALCULATE(DISTINCTCOUNT('Table'[Category]),FILTER('Table','Table'[Flag]=1))

     

    Works for you? Mark this post as a solution if it does!
    Check out this blog of mine: How to Export Telemetry Data from Azure IoT Central into Power BI

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

      Thanks Shaurya for your response.

      Since I'm using the tabular model, I can only create measures. I can't create the "Flag" as it should be created as a new "column".