Forum Discussion
Slicer to Update on Two Table using M Query
I have been having this trouble which I need to filter two tables before joining it into one table. This filtering needed has to be selected by user. I've been stumbled upon Dynamic M Query and I think this is my best solution. Would appreciate if anyone can help me building this feature (as I am not familiar and still learning using M Language).
I have these two table as below:
I need to combine these two table.
Before combining user have to select which PRODUCT TYPE they need to view (FMC or NC)
After that, the second filter that user will have to select is the month. Let say user select MONTH 7, Table 1 will show rows from MONTH 1 until 6 and from Table 2 will display MONTH 7 until 12. And let say user select MONTH 9, Table 1 will show rows from MONTH 1 until 8 and from Table 2 will display MONTH 9 until 12.
Would appreciate if someone can help me with this.
The M queries are not affected by user interactions (although they can be parameterized...).
I'd recommend appending these tables, defining a parameter table for the months to use in a slicer, and a measure that switches from returning actual to forecast based on the month selected by the slicer. E.g.
SwitchMeasure = VAR SelectedMonth = SELECTEDVALUE ( param[Month] ) RETURN CALCULATE ( [measure1], Table1[Actual/Forcast] = "ACT", Table1[Month] < SelectedMonth ) + CALCULATE ( [measure1], Table1[Actual/Forcast] = "FC", Table1[Month] >= SelectedMonth )where [measure1] is whatever metric you're interested in computing like SUM ( Table1[Sales] ) or whatever.
1 Reply
The M queries are not affected by user interactions (although they can be parameterized...).
I'd recommend appending these tables, defining a parameter table for the months to use in a slicer, and a measure that switches from returning actual to forecast based on the month selected by the slicer. E.g.
SwitchMeasure = VAR SelectedMonth = SELECTEDVALUE ( param[Month] ) RETURN CALCULATE ( [measure1], Table1[Actual/Forcast] = "ACT", Table1[Month] < SelectedMonth ) + CALCULATE ( [measure1], Table1[Actual/Forcast] = "FC", Table1[Month] >= SelectedMonth )where [measure1] is whatever metric you're interested in computing like SUM ( Table1[Sales] ) or whatever.