cancel
Showing results for
Did you mean:
Regular Visitor

## Would appreciate some help with DAX syntax for a series of AND statements in a calculation

I'm having trouble using DAX intellisense to find the columns MonthYear and TenuredStatus.  I'm learning it needs an aggregator or something to propagate. But, the values are strings. So, nothing I have tried is working.

Here is what I am trying to do in English...I just can't figure out how to say it in DAX.  I would put a several months worth of target amounts in calculation.

Monthly Target =

IF Table1[MonthYear] = "SEP2019"

AND Table1[TenuredStatus] ="Non-Tenured"

THEN 200

ELSE 300

ELSEIF

IF Table1[MonthYear] = "OCT2019"

AND Table1{TenuredStatus] = "Non-Tenured"

Then 400

Else 500

*The values in "Tenured Status" are either "Non-Tenured" or "Tenured"

Thanks in advance if anyone has a suggestion.

1 ACCEPTED SOLUTION
Super User

Hi @Wilson6327 ,
Not sure if this is the logic that you had in mind, but this will give you a start.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

``````cost =
SWITCH (
TRUE (),
MAX ( Tenure[Month year] ) = "SEPT2019"
&& MAX ( Tenure[Tenured Status] ) = "Non-Tenured", 200,
MAX ( Tenure[Month year] ) = "SEPT2019"
&& MAX ( Tenure[Tenured Status] ) = "Tenured", 300,
MAX ( Tenure[Month year] ) = "OCT2019"
&& MAX ( Tenure[Tenured Status] ) = "Non-Tenured", 400,
MAX ( Tenure[Month year] ) = "OCT2019"
&& MAX ( Tenure[Tenured Status] ) = "Tenured", 500
)``````

Proud to be a Super User!

2 REPLIES 2
Super User

Hi @Wilson6327 ,

Are you trying to build a measure or a column?
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Proud to be a Super User!

Super User

Hi @Wilson6327 ,
Not sure if this is the logic that you had in mind, but this will give you a start.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

``````cost =
SWITCH (
TRUE (),
MAX ( Tenure[Month year] ) = "SEPT2019"
&& MAX ( Tenure[Tenured Status] ) = "Non-Tenured", 200,
MAX ( Tenure[Month year] ) = "SEPT2019"
&& MAX ( Tenure[Tenured Status] ) = "Tenured", 300,
MAX ( Tenure[Month year] ) = "OCT2019"
&& MAX ( Tenure[Tenured Status] ) = "Non-Tenured", 400,
MAX ( Tenure[Month year] ) = "OCT2019"
&& MAX ( Tenure[Tenured Status] ) = "Tenured", 500
)``````

Proud to be a Super User!

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors