Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Count only related/selected values

Hi I have following data.

 

Table Sheet1:

 

Consumer_NameConsumer_ColumnImmidiate_SourceImmidiate_ColumnNew Ultimate Source SystemUltimate Source ColumnDW_AVAIL
DRDCTerm1ImmidiateImmidiate_TERM1SourceUt_term1Y
DRDCTerm2ImmidiateImmidiate_TERM2SourceUt_term2Y
DRDCTerm3ImmidiateImmidiate_TERM3SourceUt_term3N
FDGGTerm4ImmidiateImmidiate_TERM1SourceUt_term4Y
FDGGTerm5ImmidiateImmidiate_TERM5SourceUt_term5N

 

Table: Sheet2

 

Consumer_NameConsumer_ColumnImmidiate_SourceImmidiate_ColumnDW_AVAIL
ImmidiateImmidiate_TERM1ImmidiateImmidiate_TERM1Y
ImmidiateImmidiate_TERM2ImmidiateImmidiate_TERM2Y
ImmidiateImmidiate_TERM3ImmidiateImmidiate_TERM3N
ImmidiateImmidiate_TERM4ImmidiateImmidiate_TERM4N
ImmidiateImmidiate_TERM5ImmidiateImmidiate_TERM5N
ImmidiateImmidiate_TERM6ImmidiateImmidiate_TERM6N

 

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

 

 

 

 

 

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous 

    You may create a measure like below:

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

    Regards,

    Cherie

1 Reply

  • v-cherch-msft's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Anonymous 

    You may create a measure like below:

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

    Regards,

    Cherie