cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## DISTINCTCOUNT with conditions across several tables

Hi All,

Was wondering if you may be able to assit with a scenario that has me stumped.

I have 3 x tables in my model:

* A 'Key Date' Table with a column of dates

* A 'Transactions' table with a unique Client ID, transaction date and transaction amount

* A 'Client Details' table linked to the 'Transactions' table through unique Client ID

I'm then trying to create a calculated column on the 'Key Date' table which will count the number of clients who meet 2 criteria:

* Client has a 'date became client' within 4 months of key date; and

* Client has not made a transaction as of the 'key date'

Another scenario I'm trying to determine is a count of clients who have made a transaction within 13 months of each key date.

Any assistance is greatly appreciated!

1 ACCEPTED SOLUTION
Community Support

Hi @Paulo84 ,

Check the formula.

``````Column 3 =
CALCULATE (
DISTINCTCOUNT ( 'Client Details'[Client ID] ),
FILTER (
'Client Details',
'Client Details'[date became client] >= EDATE ( 'Key date'[date], -3 )
&& 'Client Details'[date became client] <= 'Key date'[date]
)
)
- CALCULATE (
DISTINCTCOUNT ( 'Client Details'[Client ID] ),
FILTER ( Transactions, Transactions[transaction date] = 'Key date'[date] )
)
``````

Here's my sample data and result.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
4 REPLIES 4
Community Support

Hi @Paulo84 ,

Check the formula.

``````Column 3 =
CALCULATE (
DISTINCTCOUNT ( 'Client Details'[Client ID] ),
FILTER (
'Client Details',
'Client Details'[date became client] >= EDATE ( 'Key date'[date], -3 )
&& 'Client Details'[date became client] <= 'Key date'[date]
)
)
- CALCULATE (
DISTINCTCOUNT ( 'Client Details'[Client ID] ),
FILTER ( Transactions, Transactions[transaction date] = 'Key date'[date] )
)
``````

Here's my sample data and result.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Community Support

Hi @Paulo84 ,

I'm not sure what is "date became client" in your model. Is it transaction date or another column in Client Details table?

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Frequent Visitor

Hi @v-jayw-msft ,

'Date became client' is not a transactional date - Think of it as the date they 'signed on' to become a client (this date will be prior to transacting).

It is another column in the client details table.

Super User

Hi @Paulo84 ,

Can you please share same sample data and expected result ,please see this post regarding How to Get Your Question Answered Quickly (courtesy of @Greg_Deckler) and How to provide sample data in the Power BI Forum (courtesy of @ImkeF).

Based on your data the results can vary, so if you provide a sample data it's easier to get the result you need.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.