cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
Helper V

How to use multiple criteria in a filter?

Hello All,

 

I need to calculate a variance between two fields that returns a number I can use for conditional formatting. 

 

The scenario is, I have projects that are of different types; new install, upgrades, module etc.....  Each one of these has a different metric for having the PM assigned (which is a date field in our projects table). 

New installs are Project Start+84 days

Upgrades are Project Start+56 days

Modules are Project Start +42 days.

 

I hae the following DAX that calculates the variance but I don't know how to write so I can factor in the appropriate criteria based on the Project Type?

 

PM Assigned Variance =

CALCULATE(
IF(ISBLANK(
MAXX('Projects',[PMAssigned])),"Not Assigned",
IF(DATEDIFF(
MAXX('Projects',[Project Start Date]),MAXX('Projects',[PMAssigned]),DAY)<=84,0,
IF(DATEDIFF(
MAXX('Projects',[Project Start Date]),MAXX('Projects',[CustomerReview]),DAY)<=91,1,
IF(DATEDIFF(
MAXX('Projects',[Project Start Date]),MAXX('Projects',[CustomerReview]),DAY)>=92,2,0)
)
)
)
)

I know this can be done in separate measures but I'd like to keep this only one column for PM Assigned in the table while also keeping different project types in the same table.

 

2 REPLIES 2
Super User IV
Super User IV

@Clint , Not very clear. Try like

Switch( True(),
MAXX('Projects',[PMAssigned]),"Not Assigned",
DATEDIFF(MAXX('Projects',[Project Start Date]),MAXX('Projects',[CustomerReview]),DAY)>=92,2,
DATEDIFF(MAXX('Projects',[Project Start Date]),MAXX('Projects',[CustomerReview]),DAY)<=91,1,
DATEDIFF(MAXX('Projects',[Project Start Date]),MAXX('Projects',[PMAssigned]),DAY)<=84,0,
0)

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hi Amit,

 

What I need the measure or calc column to do is evaluate the type of project as each Project type has different criteria.  I tried using this measure - and while syntactically valid, returns the wrong results.  PM Variance is a measure that returns a whole number (the difference between PM Start and PM Assigned)

 

PM Assigned Var #2 =
SWITCH(('Projects'[TypeofProject] IN {"Upgrade"}),
[PM Assigned Variance in Days]<=36,0,
[PM Assigned Variance in Days]<=51,1,
[PM Assigned Variance in Days]>=64,2,

SWITCH(('Projects'[TypeofProject] IN {"New Install"}),
[PM Assigned Variance in Days]<=84,0,
[PM Assigned Variance in Days]<=91,1,
[PM Assigned Variance in Days]>=92,2,

SWITCH(('Projects'[TypeofProject] IN {"Module"}),
[PM Assigned Variance in Days]<=39,99,
[PM Assigned Variance in Days]<=49,1,
[PM Assigned Variance in Days]>=50,2,99
)
))

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors