Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MeloKutman
Frequent Visitor

Merge tables and measure based on selected range

Hi all,

 

I am trying to do something simple (I guess) but a bit tricky and I cannot find the rigth solution. 

 

I have two different tables (these are a simplification...).

PLAN:

Product.PPlanned sell date Planned price
Bike 18/1/2018100
Bike 28/1/2018100
Bike 39/1/2018100
Bike 410/1/2018100
Bike 59/1/2018100
Bike 68/1/2018100
Bike 79/1/2018100
Bike 89/1/2018100
Bike 99/1/2018100

 

ACTUAL:

Product.AActual Sell dateActual sell price
Bike 68/1/2018100
Bike 78/1/2018100
Bike 810/1/2018100
Bike 99/1/2018100
Bike 1010/1/2018100
Bike 1110/1/2018100
Bike 1210/1/2018100
Bike 1310/1/2018100
Bike 1410/1/2018100


The first table is the Plan. It includes some products, its planned date of sell (refereing the month) and the planned price.

The second table is Actuals. it includes the products that were sold, the actual date (month) and the price.

 

Some of the produts appear in both tables, but others dont. eg. some were in the selling plan but didnt get sold, some were in the plan and got sold, and others were sold but not included in the plan.

 

So i've merged the tables with the Merge query (quite useful btw), using as key field the products.

And now I have this:

 

Product.PPlanned sell date Planned priceProduct.AActual Sell dateActual sell pricePlan ACHIEVED?
Bike 18/1/2018100   1
Bike 28/1/2018100   1
Bike 39/1/2018100   1
Bike 410/1/2018100   1
Bike 59/1/2018100   1
Bike 68/1/2018100Bike 68/1/20181002
Bike 79/1/2018100Bike 78/1/20181002
Bike 89/1/2018100Bike 810/1/20181002
Bike 99/1/2018100Bike 99/1/20181002
   Bike 1010/1/20181003
   Bike 1110/1/20181003
   Bike 1210/1/20181003
   Bike 1310/1/20181003
   Bike 1410/1/20181003

 

So far so good.

 

My goal is to be able to flag each of the scenarios. Adding a new Measure (or column...) called Plan ACHIEVED?, which I would like to report: 

1 if the product was in the plan, but NOT sold

2 if the product was in the plan and got sold

3 if the product was NOT in the plan but got sold

 

(this is very easy to do with simple IF functions)...

 

And here comes the tricky part:

I would like to do that using the date period/range that I have selected with a date slicer.

i.e. for Bike 7, if I have selected in the slicer only september, it will show that was planned for but not sold in september.

but if I select on the slicer a range of dates from aug to oct, i want it show that was planned and sold in the selected period.

 

The bit I am missing is how to do an IF function using the selected range of dates by slicer.

Any help?

 

Thanks a lot for your time. 

 

1 ACCEPTED SOLUTION
Stachu
Community Champion
Community Champion

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:

 

ProductDateActual PricePlanned price
Bike 601 August 2018100 
Bike 701 August 2018100 
Bike 801 October 2018100 
Bike 901 September 2018100 
Bike 1001 October 2018100 
Bike 1101 October 2018100 
Bike 1201 October 2018100 
Bike 1301 October 2018100 
Bike 1401 October 2018100 
Bike 101 August 2018 100
Bike 201 August 2018 100
Bike 301 September 2018 100
Bike 401 October 2018 100
Bike 501 September 2018 100
Bike 601 August 2018 100
Bike 701 September 2018 100
Bike 801 September 2018 100
Bike 901 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 ) )

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

4 REPLIES 4
v-lili6-msft
Community Support
Community Support

HI, @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

7.JPG

Step4:

Create visuals like this

8.JPG

Result:

9.JPG10.JPG

here is pbix, please try it.

https://www.dropbox.com/s/3ocguss7o371c0e/Merge%20tables%20and%20measure%20based%20on%20selected%20r...

 

Best Regards,
Lin

 

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Stachu
Community Champion
Community Champion

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:

 

ProductDateActual PricePlanned price
Bike 601 August 2018100 
Bike 701 August 2018100 
Bike 801 October 2018100 
Bike 901 September 2018100 
Bike 1001 October 2018100 
Bike 1101 October 2018100 
Bike 1201 October 2018100 
Bike 1301 October 2018100 
Bike 1401 October 2018100 
Bike 101 August 2018 100
Bike 201 August 2018 100
Bike 301 September 2018 100
Bike 401 October 2018 100
Bike 501 September 2018 100
Bike 601 August 2018 100
Bike 701 September 2018 100
Bike 801 September 2018 100
Bike 901 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 ) )

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Thanks @Stachu and @v-lili6-msft

 

I am trying Stachu's suggestion first. It seems to be working fine. But now I need to include a % for the (total price we planned) vs (the total of actual sold products) for the period, using the categories defined by the Plan achieved measure.

 

Like 2 vs (1+2)... and so on, but it is not working. 
I am using sumx and filter to create a new measure, like this:

  

Plan-and-sold = SUMX(FILTER(Append1,Append1[Plan Achieved]=2),[Price])
/ (SUMX(filter(Append1,Append1[Plan Achieved]=1),[Price])+SUMX(filter(Append1,Append1[Plan Achieved]=2),[Price]))

 

((*I m using the same fields names for the Price in both original tables so they are now "combined".)) 

 

but I get "Blank" as a result.

 

What am I missing here? 

Thanks a lot

I think SUMMARIZE per product is more appropiate here cause the [Plan Acieved] only makes sense for a given granularity
try this code - I'm not clear what's the sytnax for your [Price] measure, but hopefully it will work

Measure = 
VAR Tab = ADDCOLUMNS(SUMMARIZE(Append1,Append1[Product]),"Status",[Plan Achieved],"AggPrice",[Price])
VAR One = SUMX(FILTER(Tab,[Plan Achieved]=1),[AggPrice])
VAR Two = SUMX(FILTER(Tab,[Plan Achieved]=2),[AggPrice])
RETURN
DIVIDE(Two,One+Two)


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.