Count only related/selected values

[Anonymous]

Hi I have following data.

Table Sheet1:

Consumer_Name	Consumer_Column	Immidiate_Source	Immidiate_Column	New Ultimate Source System	Ultimate Source Column	DW_AVAIL
DRDC	Term1	Immidiate	Immidiate_TERM1	Source	Ut_term1	Y
DRDC	Term2	Immidiate	Immidiate_TERM2	Source	Ut_term2	Y
DRDC	Term3	Immidiate	Immidiate_TERM3	Source	Ut_term3	N
FDGG	Term4	Immidiate	Immidiate_TERM1	Source	Ut_term4	Y
FDGG	Term5	Immidiate	Immidiate_TERM5	Source	Ut_term5	N

Table: Sheet2

Consumer_Name	Consumer_Column	Immidiate_Source	Immidiate_Column	DW_AVAIL
Immidiate	Immidiate_TERM1	Immidiate	Immidiate_TERM1	Y
Immidiate	Immidiate_TERM2	Immidiate	Immidiate_TERM2	Y
Immidiate	Immidiate_TERM3	Immidiate	Immidiate_TERM3	N
Immidiate	Immidiate_TERM4	Immidiate	Immidiate_TERM4	N
Immidiate	Immidiate_TERM5	Immidiate	Immidiate_TERM5	N
Immidiate	Immidiate_TERM6	Immidiate	Immidiate_TERM6	N

Sheet 1 and Sheet 2 has Many to 1 relantionship on Sheet1.Immidiate_column to Sheet2.Consumer_Column.

What I want to show is:

1. If I filter Consumer_name from Sheet1, how many Sheet2 will get selected where DW_AVAIL = N .

e.g. If I select FDGG from sheet1 consumer name, output should give count of 1 i.e. Immidiate_TERM5

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

You may create a measure like below:

Count =
CALCULATE (
    COUNT ( Sheet2[Immidiate_Column] ),
    FILTER ( Sheet1, Sheet1[DW_AVAIL] = "N" )
)

Regards,

Cherie