cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Anonymous
Not applicable

## Creating custom Column based on multiple conditions and comparison

Hello there ,

I am looking for help, how can we create a dax expression based on multiple condition and comparison ? , below is the scenario which i wish to obtain,

so for example we have data as below:

 ID Date Satisfaction ExpectedColumn 1 1/1/2018 SATISFIED 1 1/31/2018 NOT SATISFIED 1 2 2/10/2018 SATISFIED 2 3/20/2018 NOT SATISFIED 2 4/19/2018 SATISFIED 3 2/10/2018 SATISFIED 4 4/19/2018 SATISFIED 4 5/20/2018 SATISFIED 4 6/21/2018 NOT SATISFIED 1 5 6/22/2018 SATISFIED 5 8/23/2018 NOT SATISFIED 1 6 8/30/2018 NOT SATISFIED 1 7 9/7/2018 SATISFIED

I have three columns in my Data that are ID, DATE and SATISFACTION, what i am trying to get is 4th "Expected Column" which will be calculated in this way,

lets say for ID 1, if the Value in SATISFACTION column is "NOT SATISFIED" for the Latest date in date column, then expected Column should display 1.

As we can see in ID 1, ID 4, ID 5, ID 6.

kindly help me out on this one please,

Thank you,

Regards,

Malav

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous,

We can create a calculated column using the formula to meet your requirement.

```excepted col =
VAR maxdate =
CALCULATE (
MAX ( Table1[Date] ),
FILTER ( ALL ( Table1 ), Table1[ID] = EARLIER ( Table1[ID] ) )
)
RETURN
IF (
Table1[Date] = maxdate
&& Table1[Satisfaction] = "NOT SATISFIED",
1,
BLANK ()
)```

For more details, please check the pbix as attached.

https://www.dropbox.com/s/y3b8yxaurvsjkpo/Creating%20custom%20Column%20based%20on%20multiple%20conditions%20and%20comparison.pbix?dl=0

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
2 REPLIES 2
Community Support

Hi @Anonymous,

We can create a calculated column using the formula to meet your requirement.

```excepted col =
VAR maxdate =
CALCULATE (
MAX ( Table1[Date] ),
FILTER ( ALL ( Table1 ), Table1[ID] = EARLIER ( Table1[ID] ) )
)
RETURN
IF (
Table1[Date] = maxdate
&& Table1[Satisfaction] = "NOT SATISFIED",
1,
BLANK ()
)```

For more details, please check the pbix as attached.

https://www.dropbox.com/s/y3b8yxaurvsjkpo/Creating%20custom%20Column%20based%20on%20multiple%20conditions%20and%20comparison.pbix?dl=0

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

Thank you very very much for your help @v-frfei-msft, this worked just great,
i am also having one another issue calculating the cumulative average, but thats a another question on discussion, still i am posting that link, please see if you can help me figure that out as well,

Regards,

Malav

Announcements

#### 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 Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors