Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Dynamic count by filtered related table

Hi Experts

 

I have two Tables A and B there is a relationship between the two tables (one to many) based in years.  i have the following measure which i am using to count the number of occurance in a particular year.

 

Count = 
VAR Records = CALCULATE(SUM(Sheet1[Count]),RELATEDTABLE(Sheet1)) 

Return
If(ISBLANK(Records),0,Records)

This mesure is in table B, Table A is Sheet 1.

Here is the problem

I have a filter on my Report which when is selected to ALL works fine, gives the correct results and all the graphs and counts are correct. i want to be able to select another product from the Products filter and recalculate table B based on my filter selction.

 

Need Help

2 Replies

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Route217,

    I can’t reproduce your design based on your information. So If possible, could you please  inform me more detailed information (such as your data sample and relationship structure, what is table A and Table B, and how do  they link ? And your expecting output) by image or drawings? Then I will help you more correctly.

    Below is my design

    Count = 
VAR Records = CALCULATE(SUM('product'[AMOUNT]),RELATEDTABLE('product')) 

Return
If(ISBLANK(Records),0,Records)

    Best Regards,

    Zoe Zhi

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      hi Zoe, firstly apologies it was late yesterday. see image of my design.