Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi 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
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThe 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.
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:
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:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThe 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.
Can you share the syntax of your measures?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI 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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
105 | |
79 | |
69 | |
62 |
User | Count |
---|---|
143 | |
104 | |
103 | |
82 | |
70 |