Count Occurences of a value based on another table

[Anonymous]

Hi Experts

I want to count the number of time Oct 2018 appear in table B, and then number of 1 in column WD in table B based on Table A..

so if i have

Table A             Table B           Table B[column WD]

Oct 2018           Oct 2018        1

Oct 2018           Oct 2018        1

Oct 2018           Oct 2018        0

so the answer on the measure formula should be 2..

WorkingDayNumber = CALCULATE(COUNTROWS('Taiwan Data'),'Taiwan Calendar'[Month Year] = 'Taiwan Calendar'[Month Year],DimDate[Month&Year],DimDate[Working Days]=1)

[parry2k]

@Anonymous so you want user to select value from table a and then give count from table b and also sum of "column wd", is this what you want?

[Anonymous]

Hi Parry2k

thanks for the feedback... and YES>

[parry2k · Accepted solution]

@Anonymous

try following, put a slicer and use table a field on the slicers

create a measure for selected valued, you can give default value at following measure in place BLANK()

selectedMonth = SELECTEDVALUE( TableA[Month], BLANK() )

measure for count and sum, update table name and field name as per your model. Use card visual and drop "total count" and "total sum" to see the values. Hope it is helpful.

Total Count = 
CALCULATE( COUNTROWS( TableB ), FILTER( TableB, TableB[Month] = selectedMonth ) )

Total Sum  =

CALCULATE( SUM( TableB[Column1] ), FILTER( TableB, TableB[Month] = selectedMonth ) )

[Anonymous]

parrk2k...

thanks for the feedback, without the slicer would the following work>>

WorkingDayNumber = CALCULATE(COUNTROWS('Taiwan Data'),FILTER('Taiwan Data','Taiwan Calendar'[Month Year] = 'Taiwan Calendar'[Month Year]),DimDate[Working Days] =1)

[Anonymous]

Parrky 2k.,..

solved it,......WorkingDayNumber = CALCULATE(COUNTROWS('DimDate'),FILTER(DimDate,DimDate[Month&Year] = 'Taiwan Calendar'[Month Year]),DimDate[Working Days]=1)

thanks for you're guidance..