Access Query to Power BI Table Challenge

[Peter_au]

Hello,

I have a query and a table in Access. I have used a query to produce a simple resultant table.

I have connected the query and table with their relationships to Power BI and I am trying to produce the same result in Power BI. 

I can produce the required result in Access, but not in Power BI.

I have a Date table with the required relationship to the tables in Power BI. I have also tried a Disconnected Date Table, but it is not an ideal solution as there are other graphs on the same page which rely on the filters.

I have tried multiple different approaches and have trolled through previous posts, but have gotten nowhere with this one.

Below is the expected result

Batch_Colour	SumOfTonnes_Blast_id
RomPad3_Finger_Gris	1152
RomPad3_Finger_Jaune	2592
RomPad3_Finger_Orange	960

 The tables and relationships

Table 1

Date_Tirage	Date_Transfert	Batch_nb	Tonnes_Blast_id
2020-07-01	2020-07-01	RomPad3_Finger_Orange_B00059	336
2020-07-01	2020-07-01	RomPad3_Finger_Orange_B00059	288
2020-07-01	2020-07-02	RomPad3_Finger_Violet_B00057	288
2020-07-03	2020-07-03	RomPad3_Finger_Gris_B00060	288
2020-07-03	2020-07-03	RomPad3_Finger_Jaune_B00062	144
2020-07-03	2020-07-04	RomPad3_Finger_Gris_B00060	336
2020-07-04	2020-07-04	RomPad3_Finger_Gris_B00060	288
2020-07-04	2020-07-05	RomPad3_Finger_Jaune_B00062	768
2020-07-05	2020-07-05	RomPad3_Finger_Gris_B00060	240
2020-07-05	2020-07-05	RomPad3_Finger_Jaune_B00062	336
2020-07-05	2020-07-05	RomPad3_Finger_BT_B00055	240
2020-07-05	2020-07-06	RomPad3_Finger_Jaune_B00062	720
2020-07-05	2020-07-06	RomPad3_Finger_Jaune_B00062	624
2020-07-06	2020-07-07	RomPad3_Finger_Orange_B00063	288
2020-07-06	2020-07-07	RomPad3_Finger_BT_B00055	48
2020-07-06	2020-07-07	RomPad3_Finger_Orange_B00063	48
2020-07-06	2020-07-07	RomPad3_Finger_BT_B00055	336
2020-07-06	2020-07-07	RomPad3_Finger_Orange_B00063	192
2020-07-06	2020-07-07	RomPad3_Finger_Violet_B00064	768
2020-07-07	2020-07-07	RomPad3_Finger_Orange_B00063	384
2020-07-07	2020-07-08	RomPad3_Finger_Orange_B00063	480
2020-07-07	2020-07-08	RomPad3_Finger_Orange_B00063	288
2020-07-08	2020-07-09	RomPad3_Finger_Orange_B00063	240
2020-07-08	2020-07-09	RomPad3_Finger_Violet_B00064	288
2020-07-08	2020-07-09	RomPad3_Finger_Violet_B00064	1104
2020-07-09	2020-07-09	RomPad3_Finger_Orange_B00063	192
2020-07-09	2020-07-09	RomPad3_Finger_Orange_B00063	144
2020-07-09	2020-07-09	RomPad3_Finger_Orange_B00063	192
2020-07-09	2020-07-09	RomPad3_Finger_Orange_B00063	144
2020-07-09	2020-07-09	RomPad3_Finger_Orange_B00063	288
2020-07-09	2020-07-09	RomPad3_Finger_Orange_B00063	48
2020-07-09	2020-07-09	RomPad3_Finger_Orange_B00063	48
2020-07-09			336
2020-07-09			288
2020-07-09			144
2020-07-09			288

Table two

Batch_nb	Open_Date	Close_Date	Batch_Colour
RomPad3_Finger_Gris_B00052	2020-06-23	2020-06-25	RomPad3_Finger_Gris
RomPad3_Finger_Orange_B00053	2020-06-26	2020-06-29	RomPad3_Finger_Orange
RomPad3_Finger_Jaune_B00054	2020-06-26	2020-06-29	RomPad3_Finger_Jaune
RomPad3_Finger_BT_B00055	2020-06-27		RomPad3_Finger_BT
RomPad3_Finger_Stérile_B00056	2020-06-29	2020-07-02	RomPad3_Finger_Stérile
RomPad3_Finger_Violet_B00057	2020-06-30	2020-07-03	RomPad3_Finger_Violet
RomPad3_Finger_Orange_B00059	2020-07-01	2020-07-06	RomPad3_Finger_Orange
RomPad3_Finger_Gris_B00060	2020-07-03	2020-07-07	RomPad3_Finger_Gris
RomPad3_Finger_Jaune_B00062	2020-07-03	2020-07-07	RomPad3_Finger_Jaune
RomPad3_Finger_Orange_B00063	2020-07-07		RomPad3_Finger_Orange
RomPad3_Finger_Violet_B00064	2020-07-07		RomPad3_Finger_Violet

I am at a loss as how to do this. Any ideas?

[MFelix]

Hi @Peter_au ,

In you table the last lines don't have any batch information.

However making use of your data I was abble to make the result crrect for the two top lines Jaune and Gris, as refered believe the difference is on the missing data.

Think it has to do with the way you setup your slicers.

Check PBIX file attach.

[Peter_au]

Hi Miguel,

I think it is getting close.

The data in the table is missing because that is the reality of the input data. There is often no data for the last day and/or there may be missing dates in the table. That is why I need to have a date table attached to allow the user to chose any date available as the other graphics on the report are dependant on those consecutive dates. 

I was trying to avoid an independant date table as I would have to produce a new measure for every graphic on the page.

Cheers

Peter

[MFelix]

Hi @Peter_au ,

How do you know were the blank values go to?

When you add the blank lines in the model since theres is no Batch you will get a line with blank and the quantity related with the date.

[Peter_au]

The blank values mean there are no values on that particular date.

The table will be blank for that date. Ideally it will look like this, or nothing in the tonnes column if there is no data.

[MFelix]

Hi @Peter_au ,

So just to make it clear you want to have a single slicer for the date and then make the selection of filter according to this:

• Open date < Filter Date
• Date Tirage < Filter Date
• Closed Date > Filter date

Is that you question?

If it is this you only option as you are refering is to have a date table because of two issues:

• Two the dates are based on same table
• You have different option depending on the date so this needs to be passed by a measure with variables

[Peter_au]

The date filters are correct. They come from two different tables

The sums are based on the Batch colour.

I have tried using the selectedvalue function to pass the date selected by the user to the measure, it didn't work.

Here are the relationships.

 

[MFelix]

Can you share the syntax of your measures?

[Peter_au]

I did the query in Access and have the below table to work with.

Date_Transfert	Batch_nb	SumOfTonnes_Blast_id	Close_Date	Open_Date	Batch_Colour
2020-07-01	RomPad3_Finger_Orange_B00059	624	2020-07-06	2020-07-01	RomPad3_Finger_Orange
2020-07-02	RomPad3_Finger_Orange_B00059	336	2020-07-06	2020-07-01	RomPad3_Finger_Orange
2020-07-03	RomPad3_Finger_Jaune_B00062	144	2020-07-07	2020-07-03	RomPad3_Finger_Jaune
2020-07-04	RomPad3_Finger_Gris_B00060	624	2020-07-07	2020-07-03	RomPad3_Finger_Gris
2020-07-05	RomPad3_Finger_Gris_B00060	240	2020-07-07	2020-07-03	RomPad3_Finger_Gris
2020-07-05	RomPad3_Finger_Jaune_B00062	1104	2020-07-07	2020-07-03	RomPad3_Finger_Jaune
2020-07-06	RomPad3_Finger_Jaune_B00062	1344	2020-07-07	2020-07-03	RomPad3_Finger_Jaune

Here is my non working measure.

Tonnes = calculate(

sum(qry_Daily_Finger[SumOfTonnes_Blast_id]),

filter(all(qry_Daily_Finger),

qry_Daily_Finger[Open_Date]<[Selected_date]

&& qry_Daily_Finger[Close_Date]>[Selected_date]

&& qry_Daily_Finger[Date_Transfert]<[Selected_date]

&&qry_Daily_Finger[Date_Transfert]<=max(qry_Daily_Finger[Date_Transfert])

)

)

The Selected Date is a measure as follows

Selected_date = format(DATEVALUE([selected_Year] & "-" & [Selected_Month_Abv] & "-" & [Selected_Day_nb2]),"YYYY-MM-DD")

 selected_Year = SELECTEDVALUE((date_Tbl[Year]))

Selected_month_2 = month([Selected_date])

Selected_Day_nb2 = SELECTEDVALUE(date_Tbl[Day_nmb_2])