Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
Please can someone help me with the below.
I have two tables which are joined by a key and a measure which adds the two quantities together.
Table1 | Date | Person | Location | Qty | Key |
1st Feb | A | 1 | 100 | A1 | |
1st Feb | B | 1 | 200 | B1 | |
1st Feb | C | 1 | 300 | C1 | |
1st Feb | A | 1 | 100 | A1 | |
1st Feb | B | 1 | 200 | B1 | |
1st Feb | C | 1 | 300 | C1 |
Table2 | Date | Person | Location | Qty | Key |
2nd Feb | A | 1 | -50 | A1 | |
2nd Feb | B | 1 | -100 | B1 | |
2nd Feb | C | 1 | -150 | C1 | |
3rd Feb | A | 1 | -25 | A1 | |
3rd Feb | B | 1 | -50 | B1 | |
3rd Feb | C | 1 | -75 | C1 |
KeyTable | Key |
A1 | |
B1 | |
C1 |
I have a Table visual with a slicer on table 2.date. What I would like is for the "NetQty" to change based on the dates selected by the slicer. this works fine when the slicer range means that there is a value from table 2 included.
Table 2 Slider: Date Range = 1st Feb to 3rd feb
Table1.Person | Table1.Qty | Measure1.NetQty |
A | 200 | 125 |
B | 400 | 250 |
C | 600 | 375 |
Table 2 Slider: Date Range = 1st Feb to 2nd feb
Table1.Person | Table1.Qty | Measure1.NetQty |
A | 200 | 150 |
B | 400 | 300 |
C | 600 | 450 |
However when the range falls ourside of table 2, I'm getting a blank for Table1.Qty - even though this is a core number from table 1 and doesn't have any calculations on it. What I would like in the below is the Table1.Qty values to be the same as in the two tables above, and for the NetQty to equal just the table1 values.
Table 2 Slider: Date Range = 1st Feb to 1st Feb
Table1.Person | Table1.Qty | Measure1.NetQty |
A | 0 | |
B | 0 | |
C | 0 |
Can anyone offer advice please?
Many thanks
Gritman
Solved! Go to Solution.
Hi @gritman ,
How about appending the two table into one single table following steps below. Then you needn't create Measure1.NetQty and KeyTabl.
Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ESgjlhcSDUxImS-1YT...
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @gritman ,
How about appending the two table into one single table following steps below. Then you needn't create Measure1.NetQty and KeyTabl.
Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ESgjlhcSDUxImS-1YT...
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Great that works. thanks for your help.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |