Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Boris111
New Member

for a measure use only one slicer and avoid the rest of the slicers and relationship between tables

Dear all, 
I'm a PowerBI beginner and would appreciate your help. 
I'm working on a report which has 2 related tables.
Table "Funnel", containing Customers column, Sales manager column, opportunities column for each customer and some other attributes of the opportunities.
Table "Portfolio" containing Sales manager and Customers (all the customers in the Sales managers' portfolio which are not necessarily all represented in the Funnel Table).
On Page 1 of the report there are serveral Slicer Visualizations for serveral attributes of the Opportunities of the Funnel table among which # of Customers per Sales Manager. 
In the same Page 1 I would like to create a Column or Measure that shows the Portfolio (# of total customers) per Sales manager without being influenced by filters other than the Sales Manager filter and without being influenced by the relationship with the Funnel table given that the latter has less customers than the ones present in the Portfolio table.
Meaning that the Sales managers have open opportunities with only part of their overall Customer portfolio. 
The final goal is to create a KPI measure by Sales manager, which should be dependent only on the Sales manager Slicer, representing the % of # of customers in the Funnel vs the Total # of customers in the Portfolio.
I read and tried different things but in the very end I find the Funnel==>Portfolio table relationship actually "slicing" per se the Portfolio # of customers limiting them to only the ones that appear also in the Funnel.
I'm available to further elaborate if needed. 
 

3 REPLIES 3
v-tangjie-msft
Community Support
Community Support

Hi @Boris111 ,

 

Please try creating measures.

Portfolio Customers = 
CALCULATE(
    COUNT('Portfolio'[Customers]),
    ALLEXCEPT('Portfolio', 'Portfolio'[Sales manager])
)
KPI = 
DIVIDE(
    COUNT('Funnel'[Customer]), 
    [Portfolio Customers], 
    0
)

For more information refer to

Using ALLEXCEPT versus ALL and VALUES - SQLBI

 

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

 

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

Hi Neeko, 
thanks for your reply. 
The DAX string that you propose works perfectly when used for a page in the report that uses data, and slicers, from the Portfolio table only and is used for a Visual in that page. 
When it is being used for a Visual in the page whose slicers  refer to the Funnel table (and page in the report), the Funnel table acts as a slicer and slices only those customers which are represented both in the Funnel and in the Portfolio thus leaving out (uncounted) those customers that are in the portfolio but haven't generated opportunities in the funnel.  
The ALLEXCEPT function deals with the slicers but it doesn't prevent the Funnel table to "impose" its customer perimeter given that the two tables are Related and the direction of the relationship is from the Funnel table to the Portfolio table. 
In all the things that I have read, at a certain point Bard.Google.com proposed the use of the  "UNRELATED" function which sounded promising only that "UNRELATED" function doesn't exist in PBI. 
If you still think a file is needed, I'll prepare a simpler one from scratch since I cannot share mine due to the commercial data presented inside.  

Hi @Boris111 ,

 

You can delete the relationship between the two tables. Then try the following measure .If I misunderstood you I would like you to provide virtual input data and output data, preferably pbix files (please be careful to protect your private data).

Portfolio Customers = 
CALCULATE(
    COUNT('Portfolio'[Customers]),
    ALL('Portfolio'), 'Portfolio'[Sales manager]=MAX('Portfolio'[Sales manager]))
)

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.