show data from diff table based on key made of col A and Col B against month column

[poojag820]

I need to show data into table A from table B based on Key made of Col A and Col B (ColA&ColB). This data into table A from table B is being mapped based on the Key irrespective of any date but need to show against Month column( whatever month is selected)

I am using below dax 

Sales =

VAR _selectedKey = VALUES(tableA[Key])

VAR _salesSum = CALCULATE(

    CALCULATE(

        SUM(tableB[Sales]),

        ) ,

    FILTER(

        ALL(TableB),

        TableB[Key] IN _selectedKey

    )

)

RETURN

_salesSum

This is working fine for single month selection, but total not working for multiple month selection. I think this is happening as  I have not used any date col in key but data is being shown againset month col.  any help is appreciated. 

[rajendraongole1]

Hi @poojag820 -you can adjust your DAX measure to work for both single and multiple month selections

Sales =
VAR _selectedKey = VALUES(tableA[Key])
VAR _salesSum =
CALCULATE(
SUM(tableB[Sales]),
FILTER(
ALL(TableB),
TableB[Key] IN _selectedKey &&
TableB[Month] IN VALUES(tableA[Month]) -- Match the Month context
)
)
RETURN
_salesSum

Hope this works.

[poojag820]

this condition will map values corresponding to Date also but I need to map values irrespective of date from table B into table A and show it in front of month of Table A.

I am mapping values with Key which does not have date in it. 

[Anonymous · Accepted solution]

Hi, @poojag820 

You can try to create an inactive relationship between the two tables based on the Key field and activate it when it is used.

Sales by YearMonth = 
VAR __tableAKey =
    SELECTEDVALUE ( TableA[Key] )
VAR __result =
    CALCULATE (
        SUM ( TableB[Sales] ),
        FILTER ( TableB, TableB[Key] = __tableAKey ),
        RELATEDTABLE ( TableB )
    )
RETURN
    __result

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum