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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
c13pan
New Member

How to put more than one condition from filter on my measure?

I have created cummulative data in new measure called Running Total Measure  however I need this measure to show based on slicer as well if I select more than one filter, it should show on these conditions as well if no  option is selected show all.

 

Now it is working only if I select one condition if I don't select any or select more than one, it will show wrong data

 

Running Total =
CALCULATE (
SUM ('SLM'[AnnualCost]),
FILTER (
ALL ('SLM),
('SLM'[CreatedOn] <= MAX ('SLM'[CreatedOn]))&&'SLM'[Sites.Name]=SELECTEDVALUE('SLM'[Sites.Name])&&'SLM'[Statuses.Name]<>"Closed"  && 'SLM'[FacilityHierarchyLevels.Name]=SELECTEDVALUE('SLM'[FacilityHierarchyLevels.Name])
)
)

 

 

 

 
1 ACCEPTED SOLUTION

@c13pan  for multiple values

you do

SLM'[Sites.Name] in values('SLM'[Sites.Name])

 

example

 

 

CALCULATE (
SUM ('SteamLeak Main'[LeakCosts.LostOpportunityCostAnnually]),FILTER ( 'SteamLeak Main', 'SteamLeak Main'[Statuses.Name]<>"Closed"&&'SteamLeak Main'[Statuses.Name]<>"Repair Complete" && SLM'[Sites.Name] in values('SLM'[Sites.Name]) ), filter(ALL('Date'), 'Date'[Date] <= Max('Date'[Date]) ))

 

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@c13pan , Usually we try to use slicer from the same table or related table and use cumulative logic using date table (Date table connected with date of your table)

 

Logic will like this

CALCULATE (
SUM ('SteamLeak Main'[LeakCosts.LostOpportunityCostAnnually]),FILTER ( 'SteamLeak Main', 'SteamLeak Main'[Statuses.Name]<>"Closed"&&'SteamLeak Main'[Statuses.Name]<>"Repair Complete"), filter(ALL('Date'), 'Date'[Date] <= Max('Date'[Date]) ))

 


To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

let us say we do like your advice how can i pass multiple values from slicer to this measure can you help pleasE?

 

@c13pan  for multiple values

you do

SLM'[Sites.Name] in values('SLM'[Sites.Name])

 

example

 

 

CALCULATE (
SUM ('SteamLeak Main'[LeakCosts.LostOpportunityCostAnnually]),FILTER ( 'SteamLeak Main', 'SteamLeak Main'[Statuses.Name]<>"Closed"&&'SteamLeak Main'[Statuses.Name]<>"Repair Complete" && SLM'[Sites.Name] in values('SLM'[Sites.Name]) ), filter(ALL('Date'), 'Date'[Date] <= Max('Date'[Date]) ))

 

Solved Thank you
Running Total =
VAR SelectUnit = ALLSELECTED('SLM'[FacilityHierarchyLevels.Name])
Return
CALCULATE (
SUM ('SLM'[AnnualCost]),
FILTER (
ALL ('SLM),
('SLM'[CreatedOn] <= MAX ('SLM'[CreatedOn]))&&'SLM'[Sites.Name]=SELECTEDVALUE('SLM'[Sites.Name])&&'SLM'[Statuses.Name]<>"Closed"  &&  'SLM'[FacilityHierarchyLevels.Name] IN selectedUnit
)
)

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.