Forum Discussion
Merge tables and measure based on selected range
I'd actually model it a bit differently, using Append (the product & date need to have the same name for append to work like this), so in the end I would have this table:
Product Date Actual Price Planned price Bike 6 01 August 2018 100 Bike 7 01 August 2018 100 Bike 8 01 October 2018 100 Bike 9 01 September 2018 100 Bike 10 01 October 2018 100 Bike 11 01 October 2018 100 Bike 12 01 October 2018 100 Bike 13 01 October 2018 100 Bike 14 01 October 2018 100 Bike 1 01 August 2018 100 Bike 2 01 August 2018 100 Bike 3 01 September 2018 100 Bike 4 01 October 2018 100 Bike 5 01 September 2018 100 Bike 6 01 August 2018 100 Bike 7 01 September 2018 100 Bike 8 01 September 2018 100 Bike 9 01 September 2018 100 as you want the values to be dynamic depending on the slicer you need to use a measure (column is always static):
Plan Achieved =
VAR Planned = MAX(Data[Planned price]) VAR Actual = MAX(Data[Actual Price]) RETURN IF( ISBLANK(Planned), 3, IF( ISBLANK(Actual), 1, 2 ) )
Community SupportHI, MeloKutman
For your requirement, you want to use the date period/range with a date slicer to affect the measure.
however, Planned sell date and Actual Sell date are two different columns in the merge table, so it could be achieved
by one slicer. It must be in one column. Therefore, if you need a measure you need use Append tables
Stachu's solution works well for Measure. and you could do these to use a combination of them as below:
here is my improved method.
Step1:
Add an append table and the measure as above.
Step2:
Add a Product column for merge table by this
Product = IF(ISBLANK(Merge1[Product.P]),Merge1[ACTUAL.Product.A],Merge1[Product.P])
Step3:
Create the relationship between merge table and append table
Step4:
Create visuals like this
Result:
here is pbix, please try it.
Best Regards,
Lin