Forum Discussion

buttercream's avatar
buttercream
Icon for Helper II rankHelper II
5 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 IDDateItemPrice
1113/29/202639027
1113/29/202661333
1113/29/202621141
1124/1/202618426
1134/4/202623934
1134/4/202627623
1144/15/202674133
1144/15/202651440
1144/15/202658514
1144/15/202666826
1154/28/202673823
1165/1/202613448



Ideally a DAX column that says "Sample" for 3 Sales Ids?

Need Help
  • oussamahaimoud's avatar
    oussamahaimoud
    5 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 🙂 

9 Replies

  • oussamahaimoud's avatar
    oussamahaimoud
    Icon for Memorable Member rankMemorable Member
    5 months ago

    Hi buttercream,

    Hope you're doing well!

     

    Try this code :

     

    Sample Flag =
    VAR AllSalesIDs =
    DISTINCT( 'Sales'[Sales ID] )

    VAR TotalIDs =
    COUNTROWS( AllSalesIDs )

    -- Assign a random rank to each unique Sales ID
    VAR RankedIDs =
    ADDCOLUMNS(
    AllSalesIDs,
    "@Rank", RANDBETWEEN( 1, TotalIDs * 100 )
    )

    -- Pick the 3 Sales IDs with the lowest random rank
    VAR Top3IDs =
    TOPN( 3, RankedIDs, [@Rank], ASC )

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

    RETURN
    IF(
    CONTAINS( SelectedIDs, [SelID], 'Sales'[Sales ID] ),
    "Sample",
    ""
    )

     

    Hope this helps! Don't forget to mark as solution and thumbs up in order to keep helping others.

    • buttercream's avatar
      buttercream
      Icon for Helper II rankHelper II
      5 months ago

      Hey man.  Thanks for this but my Power BI just hangs when using it.

      • oussamahaimoud's avatar
        oussamahaimoud
        Icon for Memorable Member rankMemorable Member
        5 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",

                ""

            )

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    5 months ago

    what's the expected output? could you pls explain the calculation logic as well?