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

Try this instead:

 

Sample Flag =

VAR AllSalesIDs =

    ADDCOLUMNS(

        DISTINCT( 'Sales'[Sales ID] ),

        "@Hash", MOD( 'Sales'[Sales ID] * 2654435761, 100000 )

    )

 

VAR Top3IDs =

    TOPN( 3, AllSalesIDs, [@Hash], ASC )

 

VAR SelectedIDs =

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

 

RETURN

    IF(

        CONTAINS( SelectedIDs, [SelID], 'Sales'[Sales ID] ),

        "Sample",

        ""

    )

buttercream's avatar
buttercream
Helper II
3 months ago

Only the blanks are getting flagged



A measure or a calculated table is fine too.

 

  • 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 🙂