Measure to filter rows Using another measure DAX

Question

In summary, this measure identifies sales quantities that deviate significantly from the user's average sales within a year for a specific product brand, helping to flag unusual sales patterns.User Year Decalartion Anomaly = var UserYearProductMean = CALCULATE(&nbsp; &nbsp; AVERAGE(ft_qualityesod[quantity]),&nbsp; &nbsp; ALLEXCEPT(pbidimusers, pbidimusers[username]),&nbsp; &nbsp; ALLEXCEPT(DateTable, DateTable[Year]),&nbsp; &nbsp; ALLEXCEPT(DimProduct, DimProduct[category], DimProduct[product_line]),&nbsp; &nbsp; ft_qualityesod[brand] = "ttm")// User-Year-Product Standard Deviationvar UserYearProductStdDev = CALCULATE(&nbsp; &nbsp; STDEV.P(ft_qualityesod[Quantity]),&nbsp; &nbsp; ALLEXCEPT(pbidimusers, pbidimusers[username]),&nbsp; &nbsp; ALLEXCEPT(DateTable, DateTable[Year]),&nbsp; &nbsp; ALLEXCEPT(DimProduct, DimProduct[category], DimProduct[product_line]),&nbsp; &nbsp; ft_qualityesod[brand] = "ttm")// User-Year-Product Anomaly DetectionVAR Quantity = SUM(ft_qualityesod[quantity])VAR Mean = UserYearProductMeanVAR StandarDeviation = UserYearProductStdDevVAR Threshold = 1.96 * StandarDeviationVAR UpperBound = Mean + ThresholdVAR LowerBound = Mean - Thresholdvar result = IF(&nbsp; &nbsp; &nbsp;Quantity &gt; UpperBound || Quantity &lt; LowerBound,&nbsp; &nbsp; 1,&nbsp; &nbsp; 0)RETURN result---------------------------------------------This is the result of my measure :&nbsp;we have a user name, product_line, category on row, year and month on columns and&nbsp; the measure&nbsp;&nbsp;User Year Decalartion Anomaly&nbsp;as value:&nbsp;Based on that measure, I want to create a new measure that uses this result to achieve the following:The expected output is:Create a new measure that allows me to get the rows where there is at least one '1' in the first measure.User name Product line&nbsp; Category&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MeasureValue&nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; PL&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;new&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 ( I don't have any 1 in the value)A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; PL&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;retread&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 (I have 1 in the month 10 on a year 2023)&nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; all&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp;(I have 1 in the month 10 on a year 2023)&nbsp;

greg_deckler · Answer

OmarSek75&nbsp;Sounds like a Complex Selector:&nbsp;(3) The Complex Selector - Microsoft Fabric Community
&nbsp;
Sorry, having trouble following, can you post sample data as text and expected output?Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490The most important parts are:1. Sample data as text, use the table tool in the editing bar2. Expected output from sample data3. Explanation in words of how to get from 1. to 2.
&nbsp;

omarsek75 · Answer

I corrected the post. I can't post the sample, but I tried to be as clear as possible in my explanation.

Forum Discussion

Measure to filter rows Using another measure DAX

2 Replies

Related content

Cumulative Difference from Another measure

Filter measure by another measure

Measure

DAX Measure

Measure used as a Filter breaks another measure