Forum Discussion

buttercream's avatar
buttercream
Helper II
3 months ago
Solved

DAX to flag sample based on ID

Hi,   How do I flag 3 Sales Ids randomly from a table that looks like this: Sales ID Date Item Price 111 3/29/2026 390 27 111 3/29/2026 613 33 111 3/29/2026 211 41 1...
Need Help
  • oussamahaimoud's avatar
    oussamahaimoud
    3 months ago

    Really !? 🤔

     

    Now, try this :

     

    Sample Flag =

    VAR CurrentID = 'Sales'[Sales ID]

     

    VAR AllSalesIDs =

        DISTINCT( 'Sales'[Sales ID] )

     

    VAR RankedIDs =

        ADDCOLUMNS(

            AllSalesIDs,

            "@Rand", MOD( 'Sales'[Sales ID] * 1140671485 + 128201163, 134456 )

        )

     

    VAR Top3IDs =

        TOPN( 3, RankedIDs, [@Rand], ASC )

     

    VAR IsSelected =

        COUNTROWS(

            FILTER( Top3IDs, 'Sales'[Sales ID] = CurrentID )

        ) > 0

     

    RETURN

        IF( IsSelected, "Sample", "" )

     

    Hope this helps! Don't forget to mark as solution and 👍 in order to keep helping others 🙂 

oussamahaimoud's avatar
oussamahaimoud
Memorable Member
3 months ago

Sample Flag =

VAR AllSalesIDs =

    DISTINCT( 'Sales'[Sales ID] )

 

VAR TotalIDs =

    COUNTROWS( AllSalesIDs )

 

VAR RankedIDs =

    ADDCOLUMNS(

        AllSalesIDs,

        "@Rank", RANDBETWEEN( 1, TotalIDs * 100 )

    )

 

VAR Top3IDs =

    TOPN( 3, RankedIDs, [@Rank], ASC )

 

-- Store the 3 selected IDs as a simple list

VAR SelectedIDs =

    SELECTCOLUMNS( Top3IDs, "@SelID", 'Sales'[Sales ID] )

 

-- Use COUNTROWS + FILTER instead of CONTAINS

VAR CurrentID = 'Sales'[Sales ID]

 

VAR IsSelected =

    COUNTROWS(

        FILTER( SelectedIDs, [@SelID] = CurrentID )

    ) > 0

 

RETURN

    IF( IsSelected, "Sample", "" )

buttercream's avatar
buttercream
Helper II
3 months ago

This also hangs.

  • oussamahaimoud's avatar
    oussamahaimoud
    Memorable Member
    3 months ago

    Really !? 🤔

     

    Now, try this :

     

    Sample Flag =

    VAR CurrentID = 'Sales'[Sales ID]

     

    VAR AllSalesIDs =

        DISTINCT( 'Sales'[Sales ID] )

     

    VAR RankedIDs =

        ADDCOLUMNS(

            AllSalesIDs,

            "@Rand", MOD( 'Sales'[Sales ID] * 1140671485 + 128201163, 134456 )

        )

     

    VAR Top3IDs =

        TOPN( 3, RankedIDs, [@Rand], ASC )

     

    VAR IsSelected =

        COUNTROWS(

            FILTER( Top3IDs, 'Sales'[Sales ID] = CurrentID )

        ) > 0

     

    RETURN

        IF( IsSelected, "Sample", "" )

     

    Hope this helps! Don't forget to mark as solution and 👍 in order to keep helping others 🙂 

    • buttercream's avatar
      buttercream
      Helper II
      3 months ago

      This worked!  Thank you!!!