Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
The question is about the tables with the relationship as in the image
https://drive.google.com/file/d/0B0WBkUc3tofdTVJVeFdGQll1MzQ/view?usp=sharing
I want to create a measure which calculates the sum of a value in "Sales Order - Forecasting" Table only for values where the Month of the "OrderDate" is equal to the month of "Last Date" from the custom_opportunityforecastperiod. I am unable to get this to work since the two tables are not related directly. Please help!
AmountOfForecastOppConfirmed2 = CALCULATE(
SUM('SalesOrder - For Forecasting'[SalesOrderTotalSalesPointsB]),
MONTH(OrderDate) = MONTH(custom_opportunityforecastperiod[LastDate])
)
Please note due to certain reasons I cannot add a new Date Table and link the two, I need a DAX formula to work with these
I figured this out using a Crossjoin between the tables but would also like to hear any other expert thoughts on how else it could be done. I used
AmountOfForecastOppConfirmed2 = CALCULATE(
SUM(Table1[VALUE]),
FILTER(CROSSJOIN(TABLE1, TABLE2),
MONTH(TABLE1[DATE]) = MONTH(TABLE2[DATE])
)
Hi @moizsherwani , there is nothing wrong with the cross join. You can speed it up if the tables are large by using variables and filtering each of the tables before the cross join. But don't worry about that until you need to.
Hi @moizsherwani,
After resaerch, using Crossjoin is good. I will post the update if I find new solution.
Best Regards,
Angelia
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |