Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I have a Sales Pipeline data table with three columns (Opportunity ID, Opportunity Line ID and P&C) - Each opportunity can have a single Opportunity Line ID or Multiple Opportunity Line IDs - each of these Opportunity Line Ids is either P&C or not (YES/NO in the data table) = what I am trying to do is create a filter that will only leave Opportunity IDs where all of the Opportunity Line IDs are YES in the P&C column.
Opportunity ID Opportunity Line ID P&C OP# 00287647 OL# 00394440 No OP# 00290264 OL# 00411101 Yes OP# 00291641 OL# 00412303 No OP# 00388547 OL# 00520430 Yes OP# 00415485 OL# 00482420 No OP# 00425321 OL# 00443910 No OP# 00425553 OL# 00460641 No OP# 00428801 OL# 00458754 No OP# 00430863 OL# 00450337 No OP# 00556277 OL# 00652551 Yes OP# 00556278 OL# 00652553 Yes OP# 00557461 OL# 00654044 No OP# 00560593 OL# 01421080 No OP# 00617558 OL# 00722388 No OP# 00664109 OL# 00770213 Yes OP# 00665387 OL# 00771749 No OP# 00665387 OL# 01434739 No OP# 00667607 OL# 01245284 No OP# 00687326 OL# 00806725 Yes OP# 00687329 OL# 00806730 No OP# 00687332 OL# 00806734 No OP# 00687435 OL# 00806863 Yes OP# 00687437 OL# 00806866 Yes OP# 00687440 OL# 00806869 Yes OP# 00698472 OL# 00831241 No OP# 00704537 OL# 00838439 Yes OP# 00704537 OL# 01007084 Yes OP# 00707508 OL# 00841970 Yes OP# 00709921 OL# 00845065 No
the above is a sample of my data. The Lines in Red are those which I want to get in my result. I have tried creating a column that counts the total number of Opportunity Line Ids:
Solved! Go to Solution.
Hi @Andshepch
You can create a new (filtered) table:
FilteredTable = FILTER ( Table1; VAR PC_Values_ = CALCULATETABLE ( DISTINCT ( Table1[P&C] ); ALLEXCEPT ( Table1; Table1[Opportunity ID] ) ) VAR AnyNoes_ = "No" IN PC_Values_ RETURN NOT AnyNoes_ )
or create a measure with similar code and use it in a visual level filter
Hi @Andshepch
Try this DAX statement in a calculated column to identify all Yes P&C records
AllYes = CALCULATE ( COUNTROWS ( FILTER ( OpportunityTbl, OpportunityTbl[P&C] = "Yes" ) ) )
Let us know if that works for you
Regards
David
Hi David,
Thank you for your reply.
I added that column and it correctly counted the rows where P&C is Yes, but when I try to create a formula that checks whether the total number of Yes values equals the total number of rows in the opportunity I get a circular dependency error:
Hi @Andshepch
You can create a new (filtered) table:
FilteredTable = FILTER ( Table1; VAR PC_Values_ = CALCULATETABLE ( DISTINCT ( Table1[P&C] ); ALLEXCEPT ( Table1; Table1[Opportunity ID] ) ) VAR AnyNoes_ = "No" IN PC_Values_ RETURN NOT AnyNoes_ )
or create a measure with similar code and use it in a visual level filter
Thank you for your help - I used that formula to create a new calculated table and then filtered using that and it worked exactlr as I wanted. Thanks a lot.
Andrew
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |