Showing results for 
Search instead for 
Did you mean: 
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] ))



Thanks in advance!

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).
Please show the expected outcome based on the sample data you provided.

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

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors