Accumulate distinct count with Date slicer

MinCK

Hi everyone,

Would love your expertise in this DAX. I am working on creating DAX to get the Total Distinct Count as in the example. The total distinct count the product category in each month (based on slicer), with a condition that the product must have more than 1 transaction. As in the example, when I select Quarter 1 on slicer, it should give me a total of 4 distinct product that have been selling since January.
This is the code I got so far to get the Distinct Count, but I can't find a way to get Total Distinct Count as I expect

CALCULATE(

DISTINCTCOUNT(Table[StoreID]),

FILTER(

SUMMARIZE(

Table,

Table[StoreID],

"DetailSum", COUNT(Total[ProductCat])

),

[DetailSum] > 1
)
)

Best,

danextian

Hi @MinCK

Ensure that you have a separate date dimension table where the date column has a one-to-many relationship with the fact table's date column.

Create these measures:

Distinct Count of Products with Txn > 1 = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Product Category] ),
    FILTER (
        'Table',
        COUNTROWS (
            CALCULATETABLE (
                'Table',
                ALLEXCEPT ( 'Table', 'Table'[Product Category], Dates[yr mo] )
            )
        ) > 1
    )
)



Cumulative Count of Products with Txn > 1 = 
CALCULATE (
    [Distinct Count of Products with Txn > 1],
    FILTER ( ALL ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) )
)

Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Deku

If the total ignores the current month, but still respects the date slicer, we can use allselected( dateTable )

CALCULATE(
    DISTINCTCOUNT(Table[StoreID]),
    CALCULATETABLE(
       FILTER(
         SUMMARIZE(
            Table,
            Table[StoreID],
            "DetailSum", COUNT(Total[ProductCat])
          ),
          [DetailSum] > 1
       ),
       ALLSELECTED( DateTable )
)

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

freginier

Hey there!

To achieve the Total Distinct Count of products that have more than one transaction across months in DAX, you need to count distinct product categories while ensuring they appear more than once across the dataset.

Try using the following DAX formula:

TotalDistinctCount =
VAR ProductCount =
ADDCOLUMNS(
SUMMARIZE(
Table,
Table[ProductCat]
),
"TransactionCount", CALCULATE(COUNT(Table[ProductCat]))
)

RETURN
CALCULATE(
COUNTROWS(FILTER(ProductCount, [TransactionCount] > 1))
)

When you select Q1 in a slicer, the formula will count all product categories that appear more than once.

Hope this helps!

😁😁

Accumulate distinct count with Date slicer