topic Re: random sampling in DAX Commands and Tips

random sampling

Anonymous — Fri, 13 Mar 2020 10:21:25 GMT

How to take number of rows as input and show random set of rows ?

Re: random sampling

Anonymous — Fri, 13 Mar 2020 11:49:20 GMT

check it out here

https://community.powerbi.com/t5/Desktop/DAX-random-sample/td-p/386571

Random Selection =

VAR SampleSize = 10

VAR TableWithRand = ADDCOLUMNS ( OriginalTable, "Rand", RAND () )

RETURN TOPN ( SampleSize, TableWithRand, [Rand] )

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

Re: random sampling

Anonymous — Fri, 13 Mar 2020 11:53:17 GMT

how to take row count from users?

Re: random sampling

Anonymous — Fri, 13 Mar 2020 11:56:45 GMT

Create one table generateseries(1,1000)

then add it in slicer

Allow user to select number of samples

and undate it in above formula .

in var section add selectedvalue(table[series]) instead of 10.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

Re: random sampling

Anonymous — Fri, 13 Mar 2020 12:05:39 GMT

thanks for the reply , but not working unfortunately.

Re: random sampling

Anonymous — Fri, 13 Mar 2020 12:59:38 GMT

sorry my bad.

You can take user input here.

Because columns/tables are can not be dynamic.

They will get loaded at first load only.

So the formula will work for static values only not for dynamic values.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

Re: random sampling

Anonymous — Fri, 13 Mar 2020 14:18:53 GMT

Of course, you can do that. Don't pay attention to those who say it's not possible. I know It IS possible. I'll have a think about it later today since right now I don't have time to create the solution.

Best
D

Re: random sampling

Anonymous — Fri, 13 Mar 2020 19:59:26 GMT

Hi there. I'll give you an idea about how to do this. This is probably the only way to do it with the current version of DAX and Power BI.

First, let's say you have a table (call it 'Data') in the model with N rows which are numbered consecutively, say, from 0 to (N-1) (the column is called Index). Now, you'll construct a table ('Samples') with these properties:

The first column will hold numbers from 1 to K (there'll be many rows for each value).
The second column will hold the indexes of rows that will be selected for a particular k and SampleId.
The third column will hold the SampleId.

Here's the structure:

k	Index	SampleId
1	2	1
1	4	2
1	9	3
2	1	1
2	6	1
2	8	2
2	3	2
3	4	1
3	2	1
3	8	1

...

Can you see what's going on here? The k column will be sitting on one slicer, the SampleId on another. When you select a value of k, the other slicer will adjust the SampleId from 1 to the number of samples for that particular k. Now you just have to select a sample from the range slicer. This is how you'll obtain a (pseudo-)random selection of rows.

For each k you have to create samples where each sample will be a unique combination of the indexes of the rows to select. For k = 1, you only need N samples to cover all possibilities. For k = 2, you need N*(N-1)/2 combinations, for k = 3, you need C(N, k) combinations... but this number of samples will be going up quickly as k increases. So, when k is relatively big you don't need to cover all combinations. Just pick enough of them. Once you have the above table, you can write a measure [Select Row] that will return 1 if the row should be selected and 0 if not. Here it is:

[Select Row] = var __k = SELECTEDVALUE( 'Samples'[K] ) var __oneSampleSelected = HASONEVALUE( 'Samples'[SampleId] ) var __currentRowid = SELECTEDVALUE( 'Data'[Index] ) var __selectRow = __currentRowid in VALUES( 'Samples'[Index] ) return if( __oneSampleSelected, 1 * __selectRow )

The visual in which you've placed the rows of the Data table should have a filter on it so that a row will display when [Select Row] is 1.

Best

Re: random sampling

Anonymous — Tue, 17 Mar 2020 05:09:00 GMT

Thank you vey miuch ! Will try this and update how it goes