Forum Discussion

SteveD2's avatar
SteveD2
Resolver I
7 years ago

create a virtual table for multiple measures

Hi again, I might be viewing the problem incorrectly or there's another function I'm yet to familiarise myself with but I have a table of 65,000 uniquely identified locations with Latitude and Longi...
d_gosbell's avatar
d_gosbell
Super User
7 years ago

> The problem is that filtering 65,000 rows to 500 can take up to 1.8sec, which isn't bad

 

Actually this is terrible. The tabular engine can filter 65000 rows in milliseconds. I just did a very simple test and was able to count a subset of 14,000 rows from a table with 63,000 rows in 46 milliseconds, but most of that time was spent rendering the results. The actual filtering in the tabular engine only took 4 milliseconds (measured using the Server Timings in DAX Studio). Obviously you are doing more complicated logic that a simple filtered count but it still sounds like there might be some issues with your measures.

 

Have you seen this video from Marco Russo which shows how to isolated performance issues in Power BI Reports? It's worth having a look at this and maybe posting back here if you find some problem measures but are not sure what to try to optimize them.

  • SteveD2's avatar
    SteveD2
    Resolver I
    7 years ago

    Hi, thanks for the response and yes, I have seen the video. I've been able to improve the performance by including a Keepfilters statement in the filtering statement... I don't know why it works but, it just does. So now, the basic filtering statement looks like;

    Calculatetable(
    Values(Table[Index]),
KeepFilters(
Filter(
Values(Table[Index]),
Not(Isblank([Filter Test])))
)
)

    and I use this to reduce the data set. However, I've since discovered that I have another table which I do a cross join which can create a virtual table of over 1,2M rows... Although I don't think this is an overly large table it is taking a long time to render on visuals.

    The typical coding for this type of measure looks like this;

    Var Table1=
Calculatetable(
Values(Table[Index]
// the table is filtered to reduce the data set size.

Var Table2 = 
Values(Table2[Index])

Var Final_Table =
Addcolumns(
Summarize(
Crossjoin( Table1, Table2),
Table1[Index],
Table2[Index]),
"Dist", [Distance between points measure])

Return
// as an example
Calculate(
Countrows(Final_Table),
Filter(
Final_Table,
[Dist] < x)

    apart from reducing the row count in Table2 I'm not sure what else I can do. Any suggestions would be appreciated.