Forum Discussion

mahsan_ojaghian's avatar
mahsan_ojaghian
Helper I
1 year ago
Solved

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

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 facin...
DAX
Sample
sample table
  • v-hashadapu's avatar
    v-hashadapu
    1 year ago

    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!

Sahir_Maharaj's avatar
Sahir_Maharaj
Super User
1 year ago

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's avatar
mahsan_ojaghian
Helper I
1 year ago

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()
)