Creating Sample Data set via slicer.

[MichaelDove]

Hi All,

I have a report page where a user can narrow down data in a table using 7-8 slicers on the page to view data specific to their region/division. Users still require to be able to export to .csv so they are able to investigate with the business the data they identify.

What has been requested from the business is to be able to export a random sample of the data in the table where the number of rows in the table may be quite large (thousands of rows). Yes, they could export all to .csv and filter from there, however the business require this "on Demand" sample data.

My idea is: create an unrelated table to be used on a slicer, which is used in a Dax table formula and the Sample function to either return a sample or the whole table:

Sample Data Set =
VAR sampleselect =
    SELECTEDVALUE ( 'Sample'[Sample Size] )
RETURN
    SWITCH (
        sampleselect,
        "Sample", SAMPLE (
            20,
            'Table Data Is Coming From',
            'Table Data Is Coming From'[Table Data]
        ),
        "Full Data Set", 'Table Data Is Coming From'
    )

By itself, the sample function creates a table correctly, however when adding to the Switch function, the formula doesn't allow a table to be returned as a result. 

Why doesn't switch() work, and how can I solve this??

[v-yuta-msft · Accepted solution]

Hi MichaelDove,

Power BI doesn’t support dynamic calculated table (row or column are dynamic) based on slicer because slicer selection is based on visual level but calculated table is based on data model level. You can’t change data in data model level from visual level.

If you only want to create dynamic row calculated table, you can use measure as a workaround, however, if you want to create dynamic column calculated table, Power BI doesn’t support this feature.

Regards,

Jimmy Tao