Count occurrence based on one to many relationship and based on year

[Anonymous]

Hi Experts

I have two tables A and B, in table A i have unique instance of the Client Names and in table B i have multiple instance of the Client names and Multiple intance of True or False in Column "ABC" for each client...

I want to return the count of the True values for each client name.

I have created a one to many relationship between the two tables on the client name..

Measure = CALCULATE ( COUNTROWS ( TableB ), TableB[ABC] = "true" as its text

how do i filter fo include only enters for the year 2014 in Table B column "YearC"

[Anonymous]

Measure = 
CALCULATE ( 
    COUNTROWS ( TableB ), 
    TableB[ABC] = "true",
    TableB[YearC] = 2014
)

It may be better to include the year as a filter in the report, so you won't need to create a measure for each year.

@sokg: sorry, I posted before seeing your answer, it's the same as mine

[Anonymous]

Apologies all it was my fault corrected works fine..thanks

[sokg · Accepted solution]

Two ways to do this.

1. Create a slicer from YearC column and your measure will show you the right answer, when you select 2014 or 2015 etc. (Dynamic approach).
2. Create new measure

Measure For 2014 = CALCULATE ( COUNTROWS ( TableB ), TableB[ABC] = "true", TableB[YearC]=2014)

(Static approach)

I prefer the first one.

[Anonymous]

hi Sokg

Thanks for the feedback, I am getting an error message i did try the same DAX fucntion. "the expression refers to  mulitple columns, Mulitple columns cannot be converted to a scalar value.

[sokg]

What about first approach??

Can you provide data from TableB (all columns)???