Forum Discussion

Thigs's avatar
Thigs
Helper IV
3 years ago

Pull from Random Bin

Hi! I have a rather complicated idea, so I'm simplifying it and hoping for some help. Essentially, I have a list of SKUs and a bin/location that each SKU is in. However, each SKU has three totally random locations. 

 

The locations are set at the beginning and can not repeat for the same SKU (ex - Apple's location 1 and 3 cannot both be Bin 1). 

 

Then I have some order data. For each order, a person selects the SKU randomly from it's locations (ex - they don't always pull from location 1). 

I can make the first table no problem - add three columns, use some rands and ifs to ensure they don't repeat, and we are good to go. It's the blue column in the second table I'm struggling with. How do I get Power BI to pull randomly from a row within the three columns? 

 

I have tried using Lookup Value, but that would be a set value. Maybe something like RandBetween(1,3), then lookup that column somehow?

 

Any help is greatly appreciated!!!

7 Replies

    • Thigs's avatar
      Thigs
      Helper IV
      3 years ago

      So I can Subcategory Index, but that would give a set location for each as 1 - 3, correct? That does not have randomness?

  • daXtreme's avatar
    daXtreme
    Solution Sage
    3 years ago

    First and foremost, this format:

    is not the right one to work with in PBI. PBI likes long-table format, not the one above.

    Please unpivot this table (Unpivot columns (Power Query) (microsoft.com)) to:

    SKU Location ID Location Name Location Number
    Apple 1 Location 1 1

    Apple

    2

    Location 2

    		 6
    Apple 3 Location 3 8
    Peach 1 Location 1 4
    Peach 2 Location 2 9
    Peach 3 Location 3 1

    Then, if you want to create a base table with randomly pulled Location Numbers (this will change only on data refresh), you write this caclculated table in DAX:

     

    // Orders should have a column with
// SKU so that we know which one
// to pull from the newly created
// table. We don't create any relationship
// between the tables.

[Your Table] = // calculated table
generate(
    allnoblankrow( Orders[OrderId], Orders[SKU] ),
    // Now, we'll be randomly pulling
    // a Location number from the new
    // table. Call it SKULocationLottery.
    var RandomLocationId = randbetween(1, 3)
    var CurrentSKU = Orders[SKU]
    var RandomLocationNumber =
        lookupvalue(
            SKULocationLottery[Location Number],
            SKULocationLottery[Location ID], RandomLocationId,
            SKULocationLottery[SKU], CurrentSKU,
            -1 // this should not happen!
        )
    return
        row( "Location Number", RandomLocationNumber )
)