Dynamic Sampling In Dax Commands

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

[v-sshirivolu · Accepted solution]

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)

[v-sshirivolu]

Hi @mahsan_ojaghian ,

I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

[FBergamaschi]

The code is pretty complex and long. To understand it and then fix it, we need to see it in action. 

Can you please share your pbix via some cloud service and post the link here or please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here

[mahsan_ojaghian]

Unfortunately, I can't share a file, but let me explain more simply. I want to sample a table based on a formula that dynamically calculates a ratio using page filters, and the sampling should also be dynamic based on those filters. I created a Measure to output 1 for the sampled rows and 0 for others. I want this Measure to work in any visual, showing only the sampled rows (with 1) when filtered. I've managed to get the sampled row indexes in a virtual table SelectedIDs, but since it contains multiple values, I can't match it with the main table's rows to return 1 for those rows.

I now need a method to compare the sampled indexes in SelectedIDs with the main table's indexes row by row and return 1 if they match. Any suggestions to solve this?