Forum Discussion

annag815's avatar
annag815
Frequent Visitor
6 years ago
Solved

Random Sample of Data

Hi, this is my first time posting here so apologies if I am doing something wrong.

 

I am trying to return a table with a random sample of data. So far I have a new table: 

TableWithRand =

VAR SampleSize = 10
VAR SampleSize5= .05 * COUNTROWS(Sheet1)
VAR TableWithRand =
ADDCOLUMNS ( Table1, "Rand", RAND () )
RETURN
TOPN(SampleSize5, TableWithRand, [Rand])
 
And that works. However I want to modify it and add a condition like this: 
RETURN
If (SampleSize5 > 10,TOPN(SampleSize5, TableWithRand, [Rand]), TOPN(SampleSize, TableWithRand, [Rand]) )
 
and I get a "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." 
 
Feels like I am missing somethign really simple. Any ideas? Thanks in advance!
 
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    annag815 - Equations that use SWITCH and IF to return different tables tend to fail. Create a variable, MySampleSize that chooses between SampleSize5 and SampleSize. Then use this in a single TOPN function.

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    annag815 - Equations that use SWITCH and IF to return different tables tend to fail. Create a variable, MySampleSize that chooses between SampleSize5 and SampleSize. Then use this in a single TOPN function.

  • ysf's avatar
    ysf
    Helper I
    5 years ago

    Hi there Greg_Deckler 

     

    I have this dax code which works well but now i want to make it dynamic

     

    Sample Table = Sample(VALUE("45"),FILTER('TableReload', 'TableReload'[Product House] = "ASH") ,0)
     
    Firstly the Product House changes
    I want to have a user select on the dropdown of Product house and thereafter see sample which relates to selection 
    if no selection is made then to return sample of whole table.
     
    Please asssist me.
     
    I have tried if statement and saw that they don't work
    I have tried varibales and even combined with measure for the selected value but does filter sample to value.
    --Sample(VALUE("45"),FILTER('TableReload', 'TableReload'[Product House] = [Selected Product]) ,0)