Forum Discussion

mahsan_ojaghian's avatar
1 year ago
Solved

Dynamic 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 ...
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi mahsan_ojaghian ,
    To resolve this, use CONTAINS, which can work with a table and column pair. This allows you to compare the current row's RowID with the sampled IDs table.

    Try this measure:

    IsSelected =
    VAR _seed = SELECTEDVALUE('Seed'[Value], 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 InSetWithRand =
       ADDCOLUMNS(
           InSet,
           "__Rand",
           VAR rid = 'FACT ZWARRBASE_DURABILTY'[RowID]
           VAR h =
               MOD(
                   MOD(VALUE(rid) * 131071, 1000003) +
                   MOD(_seed * 524287, 1000003),
                   1000003
               )
           RETURN h
       )

    VAR SelectedInWarranty =
       TOPN(
           SampleN,
           InSetWithRand,
           [__Rand], ASC,
           'FACT ZWARRBASE_DURABILTY'[RowID], ASC
       )

    VAR SelectedIDs =
       SELECTCOLUMNS(SelectedInWarranty, "__RowID", [RowID])

    VAR _rowid   = SELECTEDVALUE('FACT ZWARRBASE_DURABILTY'[RowID])
    VAR _status  = SELECTEDVALUE('FACT ZWARRBASE_DURABILTY'[Warr_Period_status])

    RETURN
    SWITCH(
       TRUE(),
       _status = "returned after the warranty", 1,
       _status = "returned in warranty period" &&
           CONTAINS(SelectedIDs, [__RowID], _rowid), 1,
       0
    )
    Key change: CONTAINS(SelectedIDs, [__RowID], _rowid)