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
jfbonterra
Frequent Visitor

Create measure that can be segmented by the same column used as a filter in the measure?

Hi, I want to create a measure with a few filters. One of the filters is also a column I want to segment the data by in a visual.

 

For example, I want to see all Sales, filtered by Sales Territory <> West, then display the Sales by Territory.

 

When I try to do this, I get one aggregate value, rather than data by the segment.

 

I believe it has something to do with measures and scalar values but I'm too green to know the true issue. Below is my dax. Is someone able to weigh in on how this would be accomplished?

 

StageBeforeClose Count =
var StageBeforeClose = CALCULATETABLE (
ROW ( "Closed Lost", [Total Closed Count] ),
KEEPFILTERS ( TREATAS ( { "Closed Lost" }, Opportunity[Pipeline_Status] )),
NOT ISBLANK ( Opportunity[Stage Before Close] ),
USERELATIONSHIP( 'Date'[Date], Opportunity[CloseDate] )
)
return StageBeforeClose

 

* Total Closed Count is a stand alone measure - Total Closed Count = CALCULATETABLE (
    ROW( "Bookings", [Total Opportunities Count] ),
KEEPFILTERS( TREATAS( {"Closed Won", "Closed Lost"}, Opportunity[Pipeline_Status] )),    
USERELATIONSHIP ( Opportunity[CloseDate], 'Date'[Date] ))

 
jfbonterra_1-1677249492823.png

 

Thanks in advance!

2 REPLIES 2
lbendlin
Super User
Super User

You probably don't need any of this. Familiarize yourself (and your users) with the filter pane and allow your users to change filter types.

 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Thanks for the reply.

 

Sorry, I'm not quite following. How would this be addressed in a filter pane? I have a field titled Stage Before Close and I want to display data the number of records per stage. The measure I created is giving me one value. So if I add a filter pane for Stage Before Close, it's going to give me the same value, regardless of what I filter on. Apologies if I'm not understanding your suggestion. 

 

I can work on getting sample data in the meantime. Thanks.

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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