how to filter a column in measure based on other column

[PowerrrBrrr]

So I have two table - Table A and Table B. they have one to many relationship

Table A 

ID	Class	Name
202212	LAT	Some random name
202214	LAT	Some random name
202111	LAN	Some random name
202122	LAN	Some random name

and Table 2

ID	timestamp	value1	value2
202212	2021-09-12 23:22:11	12.4	43
202212	2021-12-11 12:12:23	28.6	54
202111	2021-09-01 03:23:22	32.7	76
202111	2021-07-07 12:12:09	32.8	77

So I would like a measure where I get all Average value for each ID of Class "LAT". how can i do it in a measure??

[v-angzheng-msft · Accepted solution]

Hi, @PowerrrBrrr 

Try to create a measure like this:

ave = 
var _t=FILTER(ALL(TableA),'TableA'[Class] in {"LAT"})
var _ID_list=SUMMARIZE(_t,[ID])
var _if=IF(ISINSCOPE(TableB[ID]),IF(MAX('TableB'[ID])in _ID_list,DIVIDE(SUM(TableB[value1])+SUM(TableB[value2]),2)))
return _if

 Result:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[selimovd]

Hey @PowerrrBrrr ,

you can just calculate the average and set the filter context to LAT. The following measure should do it:

Average LAT =
CALCULATE(
    AVERAGE( 'Table B'[AverageSum] ),
    'Table A'[Class] = "LAT"
)

If you want to return only a value if LAT is in the context then you have to use the FILTER function:

Average LAT =
CALCULATE(
    AVERAGE( 'Table B'[AverageSum] ),
    FILTER(
        'Table A',
        'Table A'[Class] = "LAT"
    )
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[PowerrrBrrr]

i am soory but it seems the AVERAGESUM you have taken as one column while it is different. Also dont take the column name as Average or Sum rather just take the column name as VALUE. i want all rows of Value for class LAT. I have formatted the table now