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.
Hi,
I have 3 tables.
Table #1 is Location list
Location Contract Date
A xxx
B xxx
C xxx
D xxx
Table #2 is Asset placement per day
Date Asset Location Capacity
1-Jan-17 A1 A 10
1-Jan-17 A2 A 5
1-Jan-17 A3 B 10
1-Jan-17 A4 C 20
2-Jan-17 A1 A 10
2-Jan-17 A2 B 5
2-Jan-17 A3 B 10
2-Jan-17 A4 C 20
etc
Table #3 is Actual production for each asset per day
Date Asset Production
1-Jan-17 A1 xxx
1-Jan-17 A2 xxx
1-Jan-17 A3 xxx
2-Jan-17 A1 xxx
2-Jan-17 A2 xxx
2-Jan-17 A3 xxx
Note that
1. There's no asset placed in Location D and therefore no production for the location
2. Asset C is available in Location C (Table #2), but there's no production produced by the asset (not available in Table #3)
The report that I have to create is actual production from Table #3 per location, with all locations which still have asset available. Therefore, location A, B & C must be visible in the report, while location D should be excluded.
I'm using location from Table #1 as axis, and use production in Table #3 for the values.
But, I'm facing a difficulty to filter out Location D, location with no asset placed at.
Any input please?
Hi @RMV,
After inserting above data table into my desktop, the default relationship is like below:
Based on my understanding, if there's no asset placed in one location, then there will be no production for this location, right? If so, rather than using location from Table #1 as axis, you could use location from Table #2.
Firstly, you should create a calculated column in Table #2.
Production = LOOKUPVALUE('Table #3'[Production],'Table #3'[Date],'Table #2'[Date],'Table #3'[Asset],'Table #2'[Asset])
Then, insert a chart visual.
Best regards,
Yuliana Gu
Hi @v-yulgu-msft,
Quoted: Based on my understanding, if there's no asset placed in one location, then there will be no production for this location, right? If so, rather than using location from Table #1 as axis, you could use location from Table #2.
-> This only works 1 way. In the other hand, there could be asset placed in a location (in Table #2), but there's no production data in Table #3.
So, still need to filter the data based on the condition from Table #3.
any advise please?
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |