Distinct count on dynamic query using unrelated dimension tables

[criztina14]

I was asked to compare the number of distinct clients in the current year vs lifetime.
We're still discussing if this is really needed but I'm not sure if this would be actually possible to get.

Main Table - Current year data

StateID	CityID	ProductID	StoreID	Date	CustomerID	Amount

Lifetime Table

StateID	DistinctCountCustomers

For example, getting the #distinct customers by State, having products A, G, Z and stores 1, 2 selected in slicers is easy to get for the current year, but for the lifetime distinct count, I was just thinking it would have to be a direct query as there may be different combinations of values selected in the slicers, but I can't see how to pass the slicer values as parameters for the dinamyc query if this table won't have a relationship with those dimensions?

Any help would be appreciated. Thank you

[darkniqht · Accepted solution]

You can easily get the distinct count of customers for the current year using a measure like this:

DistinctCustomersCurrentYear =
DISTINCTCOUNT(MainTable[CustomerID])

For the lifetime distinct count, since you’re dealing with unrelated dimension tables, you can use a combination of DAX and context. Try creating a measure that calculates the distinct count while ignoring the slicers for the lifetime table:

DistinctCustomersLifetime =
CALCULATE(
    DISTINCTCOUNT(LifetimeTable[CustomerID]),
    ALL(MainTable)  // Ignores current year filters
)

To filter the lifetime data based on slicers, you can create a measure that checks for the selected products or stores from the slicers and then uses that context to filter the lifetime data:

DistinctCustomersLifetimeFiltered =
CALCULATE(
    DISTINCTCOUNT(LifetimeTable[CustomerID]),
    FILTER(
        LifetimeTable,
        LifetimeTable[StateID] IN VALUES(MainTable[StateID]) // Adjust as necessary
    )
)

If you’re using DirectQuery, you may be limited in how you pass slicer values. You might need to look into using stored procedures or creating a SQL view that combines these tables dynamically.

Finally, create visuals that show both measures side by side for comparison. This will help illustrate the differences between the distinct customer counts.

[rajendraongole1]

Hi @criztina14 - To achieve the comparison of distinct clients between the current year and lifetime, you can use a combination of measures and DAX formulas in Power BI.

Create a measure that counts distinct customers in the current year based on slicer selections.

DistinctCurrentYearCustomers =
CALCULATE(
DISTINCTCOUNT(MainTable[CustomerID]),
FILTER(
MainTable,
YEAR(MainTable[Date]) = YEAR(TODAY())
)
)

Create a measure for counting distinct customers from the lifetime table.

DistinctLifetimeCustomers =
DISTINCTCOUNT(LifetimeTable[DistinctCountCustomers])

Now. , cate a new table that contains the unique values for slicers (e.g., State, City, Product, etc.).

For example, if you're creating a table for states

StateSlicer = VALUES(MainTable[StateID])

calculates the distinct count of lifetime customers while considering the slicer selections.

DistinctLifetimeCustomersFiltered =
CALCULATE(
DISTINCTCOUNT(LifetimeTable[DistinctCountCustomers]),
LifetimeTable[StateID] IN VALUES(StateSlicer[StateID])
)

You can now create a card visual or a table visual to display both measures:

Current Year Distinct Customers: Use the DistinctCurrentYearCustomers measure.
Lifetime Distinct Customers: Use the DistinctLifetimeCustomersFiltered measure.

Hope the above measure helps in your scenerio.