cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bvy
Helper III
Helper III

How Can I Fix this DAX and Improve it for Performance?

Very simple dataset here: 

- Data - Each record represents a data point with (X, Y) coordinates; ID is unique to each point. 

IDLevelXY
1Level A5035

 

- Neightbors - A record exists in this table for each pair of ID's that are in mutually close proximity. For example the following might exist in the table, indicating that points 2 and 5 are arbitrarily close. A second row with 5 then 2 would NOT appear however: 

ID1ID2
25

 

I want to add a DAX measure to the Data table indicating, for each ID, the the number of nearby points (i.e. the number of records in the Neighbors table). Here is my DAX: 

 

ClusterMeasure =
VAR _this_id = MAX(Data[ID])
RETURN COUNTROWS(FILTER(ALLSELECTED(Neighbors), [ID1] = _this_id || [ID2] = _this_id))
 
There are a few issues.
1. I want the DAX to respond to filter selections. I have a slicer called Level, so when Level A or Level B is selected, the DAX should recalculate to count only the points in the filter context. 
2. Isolated points should ideally show a ClusterMeasure of 0. 
3. The DAX performs somewhat slow in our scaled up production application. Is there a better way to write it? 
 
Thank you. 
 
(PS: I had a PBIX ready to upload but sadly these forums don't allow it.) 
 
EDIT/PPS: I NEED A PURE DAX SOLUTION BY THE WAY. This exists in a tabular model, so altering relationships, for example, may not work. 
1 ACCEPTED SOLUTION

That's good to hear.

 

In this case, I think you can get away with essentially adding zero to the measure.

In more complicated situations this can cause unwanted zeros to appear, and may require a more complex solution (see here).

 

Maybe to be on the safe side we could include a condition that SELECTEDVALUE ( Data[ID] ) is not blank to the overall measure as well (which ensures we have filtered on a single ID):

ClusterMeasure =
VAR This_ID =
    SELECTEDVALUE ( Data[ID] )
RETURN
    IF (
        NOT ISBLANK ( This_ID ),
        VAR AllSelected_ID =
            CALCULATETABLE ( VALUES ( Data[ID] ), ALLSELECTED () )
        VAR Neighbors1 =
            CALCULATE (
                COUNTROWS ( Neighbors ),
                Neighbors[ID1] = This_ID,
                TREATAS ( AllSelected_ID, Neighbors[ID2] )
            )
        VAR Neighbors2 =
            CALCULATE (
                COUNTROWS ( Neighbors ),
                Neighbors[ID2] = This_ID,
                TREATAS ( AllSelected_ID, Neighbors[ID1] )
            )
        RETURN
            Neighbors1 + Neighbors2 + 0
    )

Regards,

Owen


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

6 REPLIES 6
bvy
Helper III
Helper III

Company policy doesn't allow it. I think there's enough info in the original post to make suggestions though. 

 

Can anyone help? 

Hi @bvy 

 

I am assuming that there are no relationships between Data & Neighbors, and that you prefer not to (or cannot) add any.

 

You could try a measure along these lines. This performs reasonably well in a test model at my end:

ClusterMeasure = 
VAR This_ID = SELECTEDVALUE ( Data[ID] )
VAR AllSelected_ID = CALCULATETABLE ( VALUES ( Data[ID] ), ALLSELECTED() )
VAR Neighbors1 =
    CALCULATE ( 
        COUNTROWS ( Neighbors ),
        Neighbors[ID1] = This_ID,
        TREATAS ( AllSelected_ID, Neighbors[ID2] )
    )
VAR Neighbors2 = 
    CALCULATE ( 
        COUNTROWS ( Neighbors ),
        Neighbors[ID2] = This_ID,
        TREATAS ( AllSelected_ID, Neighbors[ID1] )
    )
RETURN
    Neighbors1 + Neighbors2

This measure ensures that both ID1 & ID2 are in Allselected_ID.

Neighbors1 has This_ID as ID1 and Neighbors2 = has This_ID as ID2.

 

Does this give expected results for you, and is performance ok?

 

Regards,

Owen


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

Hi @OwenAuger. This actually works pretty well. Can you see a way to adjust it to return 0 for data points that have no nearby neightbors? That's the only thing that's missing. Thanks so much. 

That's good to hear.

 

In this case, I think you can get away with essentially adding zero to the measure.

In more complicated situations this can cause unwanted zeros to appear, and may require a more complex solution (see here).

 

Maybe to be on the safe side we could include a condition that SELECTEDVALUE ( Data[ID] ) is not blank to the overall measure as well (which ensures we have filtered on a single ID):

ClusterMeasure =
VAR This_ID =
    SELECTEDVALUE ( Data[ID] )
RETURN
    IF (
        NOT ISBLANK ( This_ID ),
        VAR AllSelected_ID =
            CALCULATETABLE ( VALUES ( Data[ID] ), ALLSELECTED () )
        VAR Neighbors1 =
            CALCULATE (
                COUNTROWS ( Neighbors ),
                Neighbors[ID1] = This_ID,
                TREATAS ( AllSelected_ID, Neighbors[ID2] )
            )
        VAR Neighbors2 =
            CALCULATE (
                COUNTROWS ( Neighbors ),
                Neighbors[ID2] = This_ID,
                TREATAS ( AllSelected_ID, Neighbors[ID1] )
            )
        RETURN
            Neighbors1 + Neighbors2 + 0
    )

Regards,

Owen


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

Working well. Thanks so much for taking the time. 

Fowmy
Super User
Super User

@bvy 

You can save your PBIX file on any cloud space like google drive, One Drive, etc, and share the link here.

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.