Calculated field in custom table not working

[DrewSmith]

Hi, 

I'm trying to build a visual in PowerBI that is essentially a matrix with 14 different calculations from different tables into one. 
I've managed to set up an example as below and it's pulling through the correct total figure, however when I filter for month (There is another Calendar table) the count does not change. There is a relationship that works between the Sales table and Calendar on the Use_SaleDate to Calendar[Date] (neither have time in the date field). Any idea why it's not working. It should go from 12,943 to 3,561 if the sale was in 2023 for example. 

[mlsx4]

Hi @DrewSmith 

Maybe you should include a CALCULATE in the formula:

Something similar to this:

CALCULATE (count (Sales[Use_SaleDate]),USERELATIONSHIP([date], calendar[date]))

[DrewSmith]

Thank you @mlsx4 , when added, it doesn't error but doesn't alter the figures.
I've checked and their is a relationship set up and it now reads: 

AW_Matrix_Table =

{

(2, "CS","Sales","",calculate(count(Sales[Use_SalesDate]),USERELATIONSHIP(Sales[Use_SalesDate],'calendar'[Date])))

}

[mlsx4]

Okey, then you may take the selectedvalue from the slicer?

AW_Matrix_Table =

{

(2, "CS","Sales","",calculate(count(Sales[Use_SalesDate]),SELECTEDVALUE(calendar[date])))

}

[DrewSmith]

I've tried that but it errors: The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.

So I have 14 different calculations, coming from about 10 different tables. All use a different date for example, Sale date, return date, cancellation date, so I thought creating that relationship between tables to a calendar table and using that for the slicer, and the matrix table to populate and calculate the data would be the easiest option, but it's proving far from it. 

An example of the returns calculation would be: 

AW_Matrix_Table =

{

(2, "CS","Sales","",calculate(count(Sales[Use_SalesDate]),SELECTEDVALUE(calendar[date]))),

(3, "CS","Returns","",calculate(count(Return[Use_ReturnDate]),SELECTEDVALUE(calendar[date])))

}

I could use a stack of card visuals but it's not as nice a view in my opinion.