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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
gambleave
Helper II
Helper II

Help needed with tables and filtering direction

In my data model, ServiceDetails is the main fact table with transaction records. I have a couple measures such as:

Distinct Activities = calculate(DISTINCTCOUNT(ServiceDetails[Activity ID]))

Service Status = if([Distinct Activities]>=5,2,IF([Distinct Activities]=0,0,1)). >> This is to assign a status to customers based on volume of activities

 

I want to add a slicer to a pivot table based on Addressable? in the SalesFeedbackTable on the far right, but the relationship directionality is in the wrong direction. This a Yes/No field that indicates whether particular accounts (Master Group) are relevant for particular Consultants. 

 

I tried to do this with a CROSSFILTER measure, but the slicer changes the values of my Service Status measure instead of just filtering the rows in the pivot table.  

Service Status - Addressable = CALCULATE([Service Status], CROSSFILTER (EmployeeTeams[Consultant Name], SalesFeedbackTable[Consultant Name], Both))

 

Any idea how to solve this and what I've done wrong? Thank you. 

 

datamodel.jpg

2 REPLIES 2
gambleave
Helper II
Helper II

Afraid this is all in my company system so I can't share and I'm stuck with a Mac at home. My pivot table shows Master Group (which indicates company names) as rows, and the two measures I created as Values. I have one slicer for Tiering and another slicer for Consultant Name. 

 

Service Status = if([Distinct Activities]>=5,2,IF([Distinct Activities]=0,0,1)). >> This is to assign a status to customers based on volume of activities

 

Service Status - Addressable = CALCULATE([Service Status], CROSSFILTER (EmployeeTeams[Consultant Name], SalesFeedbackTable[Consultant Name], Both))

 

As mentioned, SalesFeedback is another fact table containing a row for every combination of Master Group and Consultant Name, with a Yes or No under the "Addressable?" column to indicate whether a consultant should be servicing an account. I'm trying to add a third slicer based on this field. 

 

What I'm finding is that the use of CROSSFILTER in the second measure causes discrepancies in output of the two measures. I've never previously used CROSSFILTER and I'm struggling to understand this! I thought that toggling between Yes and No in the slicer should alternate between the Yes or the No accounts displayed, but I'm guess that it's actualling filtering out the underlying transaction records for Yes/No designated accounts before running the measure. How can I achieve what I want - i.e just display Yes or No accounts without impacting underyling calculations!

 

vanessafvg
Super User
Super User

can you share more information?

 

what do you currently have in your pivot?

can you give an example of what you are trying to display on the page and expected behaviour, its a bit difficult to understand without having a pbix or some sample data to test.





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors