Joined tables with filter returning all id's from connected table

[mblonkatf]

Hi,

I'm trying to figure how to solve this problem, I hope one of you could help.

I have two tables.

table A

id	month	value
a	jan	10
a	feb	15
b	feb	3
c	jan	15
c	feb	3
c	mar	6
c	apr	70

Table B

id	jan	feb	mar	apr
a	12	15	15	20
b	5	10	10	10
c	15	5	10	60
d	20	25	20	15
e	5	10	30	20

It is joined via id in a one(table B)-to-many(table A) relation. I would like to use the month column from table a as a filter. However I would also like to show up all id's from table B, so a, b, c, d, e and the sum of the values in regard to these months. Now I've been able to use a measure to select the values from column [jan] to the month selected from table A via the month filter. This measure is build up like this: 

if(SELECTEDVALUE('Table A'[Month])="jan";sum('Table B'[jan]);IF(SELECTEDVALUE('Table A'[Month])='feb";sum('Table B[feb]);   etc. 

This only returns the id's that the filter can return, which makes sense. Is there a way I could get all the values per id (a, b, c, d, e) from the jan column in Table B when I select "jan" from the month filter (table A)? And the same for feb,mar,etc.. 

Michel

[v-yuta-msft · Accepted solution]

@mblonkatf ,

In query editor, click on column jan, feb, mar and apr then click Transform-> Unpivot Columns, then modify the measures like:

Result =
IF (
    SELECTEDVALUE ( 'TableA'[Month] ) = "jan";
    SUM ( 'TableB'[Value] );
    IF ( SELECTEDVALUE ( 'TableA'[Month] ) = "feb"; SUM ( 'TableB'[Value] ) )
)

Community Support Team _ Jimmy Tao

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