Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
@Tomfiki can you please try this
ShipTest2 = CALCULATE([Shipments], TREATAS(VALUES(MTH_Table[MTH_Table]),DATES[Month]))
@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]>))|
Ok, perfect. Thanks again and have a nice evening. 🙂
@Tomfiki can you please try this
ShipTest2 = CALCULATE([Shipments], TREATAS(VALUES(MTH_Table[MTH_Table]),DATES[Month]))
@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?
User | Count |
---|---|
57 | |
22 | |
21 | |
19 | |
16 |
User | Count |
---|---|
87 | |
87 | |
52 | |
37 | |
23 |