Forum Discussion

craigpowell's avatar
craigpowell
Regular Visitor
8 years ago
Solved

Data refresh filter too slow

Hi All,

 

Very new (first post) to PowerBI, M and Dax. 

I am trying to filter at data refresh the lines included in the data load based on whether a value in one column exists in another column in another table.

I have two tables, StatePostcode and Address.  I have filtered the StatePostcode table to include only Victoria.

I now only want to load the rows in the Address table that relate to those Postcodes that exist in the StatePostcode table.  Each table has a Postcode column so I want to load only the rows in the Address table in which the Postcode in that row exists in the Postcode column in the StatePostcode table

I have mangaed to achieve what I want by creating a distinct query list (Postcodes) from the StatePostcode table and using it in the below filter however it is extreamly slow to the point I cannot use it in any actual application, but it (eventually) works.

 

= Table.SelectRows(_address, each List.Contains(Postcodes,[Postcode]))


Can anyone provide me with a better method/filter to achieve the same?

 

Thanks for any help.

 

  • MattAllington's avatar
    MattAllington
    8 years ago
    1. create a new query pointing to StatePostcode (call it Postcodes)
    2. Remove all colums other thatn postcode
    3. remove duplicates
    4. Set so it doesn't load.

     

    1. create a merge query starting with Address and join to Postcodes query from above
    2. left outer join
    3. expan the columns to extract the postcode from Postcodes.
    4. Filter on the new column where [postcode] <> null
    5. remove the extra postcode column.

    you are done

3 Replies

  • MattAllington's avatar
    MattAllington
    Community Champion
    8 years ago
    1. create a new query pointing to StatePostcode (call it Postcodes)
    2. Remove all colums other thatn postcode
    3. remove duplicates
    4. Set so it doesn't load.

     

    1. create a merge query starting with Address and join to Postcodes query from above
    2. left outer join
    3. expan the columns to extract the postcode from Postcodes.
    4. Filter on the new column where [postcode] <> null
    5. remove the extra postcode column.

    you are done

  • rajulshah's avatar
    rajulshah
    Resident Rockstar
    8 years ago

    Hello craigpowell , 

     

    I think using parameters will filter your table according to the PostCode entered.

     

    Please let me know if you want to use parameters and filter the table.

     

    If you dont want to use parameters,I'm afraid this solution won't be used.

     

    Thanks.