Forum Discussion
bamba98
Helper I
Helper ICreate table with random samples based on conditions from another table
Hi, I have a table consisiting of approx 50k rows and 15 columns. The file looks, for example, as followes: ID Period ID Category 1 2020 Q1 1 2 2020 Q2 1 3 2020 Q4 1 4 20...
Hi bamba98
Please try summarize function to get "Period ID" and "Category".
Rand LAST Period = VAR _LastPeriod = MAX('Table'[Period ID]) RETURN ADDCOLUMNS(SUMMARIZE(FILTER('Table','Table'[Period ID] = _LastPeriod),'Table'[Period ID],'Table'[Category]),"RAND",INT(RAND()*100))Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
bamba98Helper I
Helper II've added the following for selecting the correct number of samples:
Random Selection =
VAR SampleSize = CALCULATE(DISTINCTCOUNT(Table[Category]), ALLEXCEPT(Table, Table[Period]),FILTER(ALL(Table), Table[Latest Period]=Table[Period]))
VAR TableWithRand = ADDCOLUMNS(Table,"Rand", RAND())
RETURN TOPN(SampleSize, TableWithRand, [Rand])
Where:
Latest Period = MAX(Table[Period])This only gets me the correct number of samples to select. However, it still selects samples from different periods and same categories.
Hi bamba98
Please try summarize function to get "Period ID" and "Category".
Rand LAST Period = VAR _LastPeriod = MAX('Table'[Period ID]) RETURN ADDCOLUMNS(SUMMARIZE(FILTER('Table','Table'[Period ID] = _LastPeriod),'Table'[Period ID],'Table'[Category]),"RAND",INT(RAND()*100))Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.