Forum Discussion

bvy's avatar
bvy
Icon for Helper V rankHelper V
5 years ago
Solved

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.  ID Level X Y 1 Level A 50 35   - Neightbors - A record...
Need Help
  • OwenAuger's avatar
    OwenAuger
    5 years ago

    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

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
5 years ago

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

bvy's avatar
bvy
Icon for Helper V rankHelper V
5 years ago

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. 

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    5 years ago

    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

    • bvy's avatar
      bvy
      Icon for Helper V rankHelper V
      5 years ago

      Working well. Thanks so much for taking the time.