sampling
1 TopicDynamic Sampling In Dax Commands
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() )Solved903Views0likes4Comments
