User Profile
mahsan_ojaghian
Helper I
Joined 4 years ago
User Widgets
Contributions
Re: Create a dynamic sample table from main table based on slicer selection and append it back
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!982Views1like0CommentsRe: Create a dynamic sample table from main table based on slicer selection and append it back
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?1.1KViews0likes0CommentsRe: Dynamic Sampling In Dax Commands
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?773Views0likes0CommentsDynamic 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() )Solved903Views0likes4CommentsRe: Create a dynamic sample table from main table based on slicer selection and append it back
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() )1.3KViews0likes0CommentsCreate 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 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.Solved1.7KViews2likes9Commentscreate a measure that converts negative values to zero
Hi Guys. I'm really Confused!!! I have a table like this: ITEM_CODE NEED INVENTORY_QTY MISS_0 ITM_1 100 50 50 ITM_2 200 10 190 ITM_3 35 100 -65 I want to create a measure that converts negative values to zero. I need to sum of [MISS_0] column in a cart and it should show 240 not 175. and I need to use measure and not calculate column because I have other filters that using of calculate column does'nt helpful. Now my solution is using filter on this visual(Table) that filters Positive values but this way can't help me cause we cant filter measure with single value in a Cart. I need a measure just sum positives or in the best way converts to zero. Thanks.Solved693Views0likes1Commenta measure that sum positive values only in a Cart
Hi Guys. I'm really Confused!!! I have a table like this: ITEM_CODE NEED INVENTORY_QTY MISS_0 ITM_1 100 50 50 ITM_2 200 10 190 ITM_3 35 100 -65 I want to create a measure that converts negative values to zero. I need to sum of [MISS_0] column in a cart and it should show 240 not 175. and I need to use measure and not calculate column because I have other filters that using of calculate column does'nt helpful. Now my solution is using filter on this visual(Table) that filters Positive values but this way can't help me cause we cant filter measure with single value in a Cart. I need a measure just sum positives or in the best way converts to zero. Thanks.Solved2KViews0likes2CommentsRe: Dynamic value Changing in Calculate Column With Slicer Filter On Report Page
Hi Mr,Kim. Thanks for your response. But I can't use a measure for my calculation because I need more calculations after this step. And unfortunately can't share my pbix file with you cause my workplace doe'nt allow me to share.2KViews0likes0Comments
Data Privacy
Microsoft Fabric Community and Privacy
To learn more about how we manage your data, please review the Microsoft Fabric Community Data Privacy guide.