DAX measure to count rows filtered based on values from 2 separate table

[Anonymous]

Hi all!

Tables

Table_StoreList

Table_CustList

Table_Feedback_Received

Dim_Calendar

Relationships

'Table_Feedback_Received'[Date] -> 'Dim_Calendar'[Date]

'Table_Feedback_Received'[CustID] -> 'Table_CustList'[StoreID]

'Table_CustList'[StoreID] -> 'Table_StoreList'[StoreID]

'Table_CustList'[DateCreated] -> 'Dim_Calendar'[Date]  (inactive relationship)

There is a 'Table_CustList'[Status] with either "Active" or "Inactive".

How do I create a visual below?

__________________________________________________________________   Jan   Feb   Mar   Apr   May   Jun   Jul   Aug   Sep

Number_of_Active_Customers_with_1_or_more_Feedback YTD    |    N  |  N   |  N   |   N   |  N   |   N  |  N   |  N   |  N

I have tried with the following measure but it does not take into consideration whether the CustID is active or inactive (from Table_CustList[Status])

'Table_CustList'[Number_of_Active_Customers_with_1_or_more_Feedback = TOTALYTD(DISTINCTCOUNT('Table_Feedback_Received'[CustID]), 'Dim_Calendar Table'[Date])

Any ideas how to modify the above to get the desired visual?

Regards,

Yee Pin

[lc_finance]

Hi @Anonymous ,

Could you share a sample Power BI file?

You could share it via DropBox, One Drive, Drive or another similar tool.

A sample Power BI file will make it easier to help you.

Regards,

LC

[Anonymous]

@lc_finance  Hi LC, thanks for your attention! The pbix file contains confidential details and I am not able to share it. 

I will need time to create a sample file with sample data. Do you happen to have any hints for me in the meantime?

Regards,

Yee Pin

[lc_finance]

Hi @Anonymous ,

Yes, I am happy to share some hints.

I would do the following: use a SUMX expression that iterates the customer table and adds 1 each time the customer corresponds to the criteria.

I just wrote a blog post on something similar: iterating an Employee table to count the number of employees each month. You can find the post here, that could help you with the formulas.

I hope this is helpful, and do not hesitate to share the PBI file when you have it. I will be happy to help you further,

LC

Interested in learning Power BI and DAX? Check out my blog at www.finance-bi.com