How to use MONTH column from another CALNDER Table in DAX

[Anonymous]

Hi team

I have written following dax

sum_LTAR =

var _ltar = CALCULATE(

SUM(Sitewise_LTAR[Score]),

Sitewise_LTAR[Content] = "LTAR",

DATESMTD(Sitewise_LTAR[Month])

)

Return

IF(_ltar=0,"-",_ltar)

Which is taking all columns in one table "Sitewise_LTAR".

I have another CALENDER Table in which I have months also and have relationship with Sitewise_LTAR Table (One to Many).

Could you suggest - in what way I can use MONTH column from CALENDER table in same DAX as written above instead of using MONTH column from same table.

"DATESMTD(Sitewise_LTAR[Month])" -> DATESMTD(Calender[Month]) <- this is not working - giving problem. Please help.

Thanks and regards

RatanB

[goncalogeraldes]

If you have a relation in the model between the Sitewise_LTAR table and the Calendar table you can use the RELATED() function in your formula.

sum_LTAR =
var _ltar = CALCULATE(
SUM(Sitewise_LTAR[Score]),
Sitewise_LTAR[Content] = "LTAR",
DATESMTD(RELATED(Calendar[Your_date_column]))
)
Return
IF(_ltar=0,"-",_ltar)

Please check the following microsoft documentation for more help:

https://docs.microsoft.com/en-us/dax/related-function-dax

Hope this answer solves your problem! If you need any additional help please tag me in your reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
Thanks!

Best regards,
Gonçalo Geraldes

[Anonymous]

@goncalogeraldes Thanks for you reply.

I have been trying the same thing which you have mentioned but under DATESMTD formula - it is not allowing RELATED function - its only allowing RELATEDTABLE. 

Please advise how to proceed further

[goncalogeraldes]

It may be due to the relationship (cardinality) between both tables. This is because the RELATED() function fetches the values from one side of the relationship to the other side meaning that one-to-one relationship and one-to-many relationship work just fine with this function while the many-to-many relationship does not. Also, the one-to-many relationship only allows you to use the RELATED() when on the MANY side of the relationship, not the ONE. The reason is that the result of the RELATED function is just one value, therefore when you want to use it on the MANY sides, multiple values are returned hence the use of the RELATEDTABLE() function. The returned table is a sub-table of the given table for all records that match the key in the current row of the current table. Try combinig the RELATEDTABLE() function with other DAX functions that accept a table as the expression, for example, in your case, the SUMX() function. 

Hope this answer solves your problem! If you need any additional help please tag me in your reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
Thanks!

Best regards,
Gonçalo Geraldes