dax measure to multiply a column from a different table by a distinct count and filter of current t

[Anonymous]

I have a table of all of my customer transactions and a separate table with my sales quotas. I'm trying to get the formula to:

Distinct count the number of transaction dates in the customer transactions table, where the working day=yes. Then multiply that number of days by the daily quota.

I currently have this formula but it is giving me an error

Quota =
CALCULATE(
DISTINCTCOUNT('Customer Sales Transactions'[Transaction Date]),
FILTER('Customer Sales Transactions',
'Customer Sales Transactions'[CS Working Day]="Yes")
)
*'Sales Team Quotas'[Daily Quota])

[Floriankx · Accepted solution]

Do you want to create a measure or a calculated column?

At first it seems your last ')' is too much. Maybe this helps.

Here an alternative if not.

For Measure you could try:

Quota =
CALCULATE(
COUNTROWS(VALUES('Customer Sales Transactions)),
FILTER('Customer Sales Transactions',
'Customer Sales Transactions'[CS Working Day]="Yes")
)
*Sum('Sales Team Quotas'[Daily Quota])

In this case you need to ensure a relation between these tables.

[Anonymous]

Your alternative option worked, thank you so much!

Can you help me understand how this worked and why the 'Sum' function was used at the end?

[Floriankx]

Hello, 

you can't reference to a row in a measure so you have to wrap it with SUM, MIN, MAX etc even if there is only one row each relevant value.

Best regards.

[Anonymous]

This solution worked great, however, i have the chart connected to a slicer where i select the month i want to see, but the formula isnt calculating correctly based on the slicer. How do i get it to narrow by the time frame in the slicer?

[Floriankx]

Do you have a separate DateTable?

If not there is your problem. You have to create a DateTable with contigous dates relate the other two tables and slice by the DateTable.

If you have several years slicing by month gives you the month value of all available years.

Best regards.