Measure with values matching another table (no relationships)

[Tomfiki]

Hello all,

Could you please help me get this DAX formula working?

ShipTest2 = CALCULATE([Shipments],FILTER(MTH_Table,MTH_Table[MTH]=VALUES(DATES[Month])))

I need to have the result in the first matrix the same as in the second matrix. I was not able to figure out how to separate the numbers for the first table into months - the column MTH in MTH_table has no relationship with Month in DATES table.

The only way I was able to get this "working" was to create 12 separate measures for each month which is not very efficient, I'd say.

Please don't get into why I ask this stupid question. 😄 I need to further include in the calculation another measure with another date table.
I apologize if the solution is already here somewhere, I was not able to find it.

[smpa01]

@Tomfiki  have a read of this https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

If there is no relationship, you can use TREATAS/CONTAINS/INTERSECT

The basic way TREATAS works as 

CALCULATE(<targetMeasure>,TREATAS(VALUES/SUMMARZE(<lookupColumn>),<targetColumn>))

Translating the above

| XAxis          | Measure                                             |
|----------------|-----------------------------------------------------|
| Axis from tbl1 | CALCULATE(<AggregateonDATES[value]>,                |
|                | TREATAS(VALUES/SUMMARZE(<MTH[MTH]>),<Dates[Date]>)) |
| Axis from tbl2 | CALCULATE(<AggregateonMTH[value]>,                  |
|                |  TREATAS(VALUES/SUMMARZE(<Dates[Date]>),<MTH[MTH]>))|

[Tomfiki]

Ok, perfect. Thanks again and have a nice evening. 🙂

[smpa01 · Accepted solution]

@Tomfiki  can you please try this

ShipTest2 = CALCULATE([Shipments], TREATAS(VALUES(MTH_Table[MTH_Table]),DATES[Month]))

[Tomfiki]

@smpa01 Thank you for the reply, it works great!

Could you advise how to adjust it so if I filter something in DATES[Month] it will also reflect in the result?