Forum Discussion
Add a new column for a random sample
Hi,
I'm looking to randomly select 50% of rows in a table based on a filter, creating a new column that adds "Sampled" to the row being sampled"
I've tried the following code but it doesn't work as sample takes multiple columns into account when returning. Countblank is based on a column i created to separate low value & high value assets.
Sampled = SAMPLE(COUNTBLANK('Asset'[Blank Value]) / 2 , 'Assets', 'Assets'[RFID Tag],ASC)
e.g. if i have the below table, i want to take 50% of all rows that have a value "Low".
| ID | Value |
| 1 | Low |
| 2 | Low |
| 3 | High |
| 4 | Low |
| 5 | Low |
| 7 | High |
| 8 | Low |
| 9 | Low |
| 10 | High |
Which would create this column:
only takes into consideration low value assets, 50% Of the 6 low value assets = 3, so only 3 sampled.
| ID | Value | Sample |
| 1 | Low | Sampled |
| 2 | Low | |
| 3 | High | |
| 4 | Low | Sampled |
| 5 | Low | |
| 7 | High | |
| 8 | Low | Sampled |
| 9 | Low | |
| 10 | High |
Thank you!!
I'm not sure if I understood you correctly, but try this anyway
3 Replies
I'm not sure if I understood you correctly, but try this anyway
Hi Ahmed, Thanks for the response.
I've got as far as the return expression but as soon as i put that in it says unexpected expression return.
Do you know why this might be?
Sampled = VAR _select = "Low Value" VAR _tbl = FILTER(ALL('hardcat HardcatAssets1'),'hardcat HardcatAssets1'[High Value Asset 2]=_select) VAR _randomlyselect = COUNTROWS(_tbl) * 0.1 -- 10% VAR _SampleSize = SELECTCOLUMNS( SAMPLE(_randomlyselect,_tbl,[Asset Code],ASC,"@Asset Code",[Asset Code],"@High Value Asset 2",[High Value Asset 2]) RETURN IF(COUNTROWS(FILTER(_SampleSize,[@Asset Code]=[Asset Code]&&[@High Value Asset 2]=[High Value Asset 2])),"Sampled") //SAMPLE(COUNTBLANK('hardcat HardcatAssets1'[Blank Value]) / 10 , 'hardcat HardcatAssets1', 'hardcat HardcatAssets1'[RFID Tag],ASC)Thanks
you forgot the ) after ASC
VAR _SampleSize = SELECTCOLUMNS( SAMPLE(_randomlyselect,_tbl,[Asset Code],ASC),"@Asset Code",[Asset Code],"@High Value Asset 2",[High Value Asset 2])
