Forum Discussion

Helper III

6 years ago

Solved

Relationship with Summary Table

Hi Team,

I have a table (Table A) in the following format

Table B is defined as

Table B = SUMMARIZE('Table A','Table A'[Signature],"Event",SUM('Table A'[Event]),"Avg Alt",AVERAGEX('Table A','Table A'[Alt]))

I also have a date table. Relationship between these tables is as follows.

When I filter a date from date table, It gives me the wrong value for Event from Table B.

On 1st may 2020, the value of event should be 1.

How to resolve this issue ?

Please help

amitchandak
Super User
6 years ago
Jos13 , did not get the need of Table B, As you got that from Table A. what is that you would not have got from Table A
mahoneypat
Microsoft Employee
6 years ago
The Date table is filtering to that Date in Table A, but the bi-di relationship with Table B is on Customer, so you are getting the sum for all rows of that Customer. You should not need to create Table B and do it all with a measure on Table A. You should be able to have a simple SUM(TableA[Event]) measure and slice/dice by Signature, Date, Company, etc. in your visuals.

If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
v-lili6-msft
Community Support
6 years ago
hi Jos13

The relationship between TableA and TableB is based on [Signature], When you filter a date from date table, it will filter out [Signature] in tableA, then TableA will filter TableB only by [Signature] filed, it couldn't filter tableB by [date].

For example:

When you filter 1st may 2020, it will filter Signature is 772325 in TableA, then TableA will filter TableB by [Signature] field which it only is 772325 in TableB, so the result is 4 not 1.

For your case, you do not need a TableB, just use TableA will meet your all requirement.

Eg. you could just drag SUM of TableA[Event]

Regards,

Lin