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

Postby Steve_D » Thu Nov 08, 2018 4:07 pm

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

Code: Select all

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,

Code: Select all

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

Postby Steve_D » Fri Nov 09, 2018 9:52 am

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

Postby Oxenskiold » Sat Nov 10, 2018 2:53 am

Hi Steve_D,

Can't you use a simple FILTER()?

Code: Select all

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] )


I must admit I haven't downloaded you sample file so I may be missing something here.

Best regards Jes.

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

Re: Filtering a measure created in a virtual table

Postby Steve_D » Sat Nov 10, 2018 10:40 am

I adjusted the Dax to this;

Code: Select all

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;

Code: Select all

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;

Code: Select all

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.


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 8 guests