## Filtering a measure created in a virtual table

Anything related to PowerPivot and DAX Formuale
Steve_D
Posts: 55
Joined: Sun Jul 31, 2016 2:29 pm

### Filtering a measure created in a virtual table

I've worked my way through the creation of a virtual table that calculates the distance between Lat/Long coordinates for suburbs and Lat/Long coordinates for stores. When I materialise this virtual table through the "Make Table" function I'm able to obtain a table of Suburb Name, Store Name and Distance... which is great except it's 4.3 million rows and creates performance issues for the calculations that use the table. What I want to achieve is a means of reducing the size of the table by filtering the Distance column ie. Suburb to Store distances < 10Km.
The Dax code is

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

`VAR Final_Table =    SELECTCOLUMNS(         Crossjoin_Table, // crossjoin of Suburb, Lat, Long and Store, Lat,Long                "Sub_Name", 'Sub Heirarchy'[Name],                "Store_Name", 'Retailers Table'[Store Name],                "Sub-Store Dist", [Dist] // a calculation of distance between Suburbs and Stores... 4.3M values.                Return`

However, I've tried every possible permutation and combination of dax expressions to filter the distance values of the Sub-Store Dist field without success. The major issue being the Field is not available for use in a Calculate or Calculatetable statement and only appears in the following code,

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

`SUMMARIZE(Final_Table,[Sub_Name],[Store_Name],[Sub-Store Dist])`
which returns the 4.3M rows. Any suggestions on how I can reference this field in a Calculatetable function so I can reduce the number of rows returned by the table?

Steve_D
Posts: 55
Joined: Sun Jul 31, 2016 2:29 pm

### Re: Filtering a measure created in a virtual table

PBix file attached with sample data and make table calculation.
Last edited by Steve_D on Thu Nov 15, 2018 8:10 am, edited 1 time in total.

Oxenskiold
Posts: 36
Joined: Tue Jan 05, 2016 10:38 pm

### Re: Filtering a measure created in a virtual table

Hi Steve_D,

Can't you use a simple FILTER()?

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

`VAR Final_Table =    FILTER (        SELECTCOLUMNS (            Crossjoin_Table,            // crossjoin of Suburb, Lat, Long and Store, Lat,Long            "Sub_Name", 'Sub Heirarchy'[Name],            "Store_Name", 'Retailers Table'[Store Name],            "Sub-Store Dist", [Dist]        ),        [Sub-Store Dist] < 10    )RETURN    SUMMARIZE ( Final_Table, [Sub_Name], [Store_Name], [Sub-Store Dist] )`

Best regards Jes.

Steve_D
Posts: 55
Joined: Sun Jul 31, 2016 2:29 pm

### Re: Filtering a measure created in a virtual table

I adjusted the Dax to this;

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

`SUMMARIZE(Final_Table,[Sub_Name],[Store_Name],"Dist<2.5", FILTER(Final_Table,  [Sub-Store Dist] <= 2.5)`

the returned error was, "The expression refers to multiple columns". Which got me thinking that perhaps I don't need to define the columns to return so I adjusted the code to this;

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

`CALCULATETABLE(Final_Table,FILTER(Final_Table,  [Sub-Store Dist] <= 2.5))`
and although there was no error, all 4.3M rows were returned.

So I knew Summarize and Filter would allow me access to the field I wanted to filter so, I adjusted the code to this;

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

`SUMMARIZE(FILTER(Final_Table, [SA2-Store Dist] <= 2.5),[SA2_Name],[Store_Name],[SA2-Store Dist])`
and it worked. the returned table is only 4,615 rows in length and much more forgiving on the PC's memory and performance.