Forum Discussion

RMV's avatar
RMV
Helper V
9 years ago

filter from other table

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?

Need Help

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    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

    • RMV's avatar
      RMV
      Helper V
      8 years ago

      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?