Create a dynamic sample table from main table based on slicer selection and append it back

[mahsan_ojaghian]

How can I create a sample table from my main table in Power BI using the SAMPLE function, in such a way that I can later append this sample table back to the original table?

The challenge I am facing is that the SAMPLE function doesn’t seem to respect slicers applied in the report page. I need the sample table to be re-generated dynamically based on the slicer selections (so that new samples are created from the filtered population).

Is there a way to make the sample table fully responsive to slicers, and still be able to append it back to the main table.

[v-hashadapu]

Hi @mahsan_ojaghian , Hope you're doing fine. Can you confirm if the problem is solved or still persists? Sharing your details will help others in the community.

[mahsan_ojaghian]

Hi,

Thank you so much for your help on my question! I’m sorry for the late reply — I’ve been working through a few challenges implementing your solution and testing that the sample counts matched correctly, which took me some extra time.

I really appreciate the time and effort you put into guiding me. Your explanation was very helpful, and I’m grateful you shared your knowledge.

Thanks again!

[v-hashadapu · Accepted solution]

Hi @mahsan_ojaghian , Thank you for reaching out to the Microsoft Fabric Community Forum.

I reproduced the scenario on my end using sample data and it worked successfully. To help you better understand the implementation, I’ve attached the .pbix file for your reference. Please take a look at it and let me know your observations.

Thank you for being part of the Microsoft Fabric Community!

[v-hashadapu]

Hi @mahsan_ojaghian , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

[mahsan_ojaghian]

Thanks for checking in! My issue isn't resolved yet. I have a DAX Measure in Power BI to sample rows from a table based on a dynamic ratio from page filters. It should return 1 for sampled rows and after-warranty rows, and 0 for others. The sampling works, and I get the correct row indexes in SelectedIDs. But CONTAINSROW(SelectedIDs, _rowid) always returns FALSE when using _rowid = SELECTEDVALUE('Table'[RowID]), though it works with a fixed value like 123. I tried SUMX, but it slows down or breaks the visual due to large data. I need a way to compare each row's RowID with SelectedIDs row-by-row to return 1 for matches, without performance issues. Any ideas?

[Sahir_Maharaj]

Hello @mahsan_ojaghian,

The most common approach I would recommend is to create a calculated column.

Random = RAND()

Then in your visual, use TOPN() or a measure to dynamically restrict how many rows appear, based on slicers.

Sample Flag =
VAR _TopN = 100   -- number of rows you want sampled
RETURN
IF (
    RANKX ( ALLSELECTED ( 'MainTable' ), 'MainTable'[Random], , ASC )
    <= _TopN,
    1,
    0
)

Hope this helps - let me know if you might have any further questions.

[mahsan_ojaghian]

hello,

I have a table containing products that were returned during the warranty period and products returned after the warranty period. I want to sample a portion of the in-warranty products based on a dynamic ratio that changes with page filters. Ultimately, I need to return 1 for all after-warranty products and for the sampled in-warranty products, and 0 for others.

However, in CONTAINSROW, the second argument must be a single value, and it doesn't return TRUE when using a column or even SELECTEDVALUE. As a result, the output is BLANK. Please suggest any solutions that come to mind.
This is my current code:
IsSelected =
VAR _seed = SELECTEDVALUE('Seed'[Seed], 1)
VAR AfterSet =
CALCULATETABLE(
'FACT ZWARRBASE_DURABILTY',
KEEPFILTERS('FACT ZWARRBASE_DURABILTY'[Warr_Period] = 1),
KEEPFILTERS('FACT ZWARRBASE_DURABILTY'[Warr_Period_status] = "returned after the warranty")
)
VAR AfterCount = COUNTROWS(AfterSet)
VAR _rateRaw = [RET_RATE_ACT]
VAR _rate = MAX(0, MIN(0.999999, _rateRaw))
VAR SampleN_Base = ROUNDUP( DIVIDE( AfterCount * _rate, 1 - _rate ), 0 )
VAR InSet =
CALCULATETABLE(
'FACT ZWARRBASE_DURABILTY',
KEEPFILTERS('FACT ZWARRBASE_DURABILTY'[Warr_Period] = 1),
KEEPFILTERS('FACT ZWARRBASE_DURABILTY'[Warr_Period_status] = "returned in warranty period")
)
VAR InSetCount = COUNTROWS(InSet)
VAR SampleN = MIN(SampleN_Base, InSetCount)
VAR InSetWithKey =
ADDCOLUMNS(
InSet,
"__RowID", 'FACT ZWARRBASE_DURABILTY'[RowID]
)
VAR InSetWithRand =
ADDCOLUMNS(
InSetWithKey,
"__Rand",
VAR rid = [__RowID]
VAR h = MOD( MOD(VALUE(rid) * 131071, 1000003) + MOD(_seed * 524287, 1000003), 1000003 )
RETURN h
)
VAR SelectedInWarranty =
TOPN(
SampleN,
InSetWithRand,
[__Rand], ASC,
[__RowID], ASC
)
VAR SelectedIDs =
SELECTCOLUMNS(SelectedInWarranty, "__RowID", [__RowID])
RETURN
IF(
HASONEVALUE('FACT ZWARRBASE_DURABILTY'[RowID]) &&
HASONEVALUE('FACT ZWARRBASE_DURABILTY'[Warr_Period_status]) &&
SELECTEDVALUE('FACT ZWARRBASE_DURABILTY'[Warr_Period]) = 1,
VAR _rowid = SELECTEDVALUE('FACT ZWARRBASE_DURABILTY'[RowID])
VAR _status = SELECTEDVALUE('FACT ZWARRBASE_DURABILTY'[Warr_Period_status])
RETURN
IF(
_status = "returned after warranty period", 1,
_status = "returned in warranty period" && NOT ISBLANK(_rowid) && CONTAINSROW(SelectedIDs, _rowid), 1,
0
),
BLANK()
)

[FBergamaschi]

Thanks for the kudo. If this was a solution, please mark it as such so others can benefit from this information

Thanks

[FBergamaschi]

In Tabular, tables cannot change dinamically. Once SAMPLE has created the table, the table is that one and cannot change. If you explain what you want to do, we can try helping you finding another approach to reach your goals.

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI