cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
NilPil Frequent Visitor
Frequent Visitor

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

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Count only related/selected values

Hi @NilPil 

You may create a measure like below:

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

1.png

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Community Support Team
Community Support Team

Re: Count only related/selected values

Hi @NilPil 

You may create a measure like below:

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

1.png

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.