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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
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:

 

IDDateSatisfactionExpectedColumn
11/1/2018SATISFIED 
11/31/2018NOT SATISFIED1
22/10/2018SATISFIED 
23/20/2018NOT SATISFIED 
24/19/2018SATISFIED 
32/10/2018SATISFIED 
44/19/2018SATISFIED 
45/20/2018SATISFIED 
46/21/2018NOT SATISFIED1
56/22/2018SATISFIED 
58/23/2018NOT SATISFIED1
68/30/2018NOT SATISFIED1
79/7/2018SATISFIED 

 

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
v-frfei-msft
Community Support
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 ()
    )

Capture.PNG

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.

View solution in original post

2 REPLIES 2
v-frfei-msft
Community Support
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 ()
    )

Capture.PNG

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, 

 

link: 

https://community.powerbi.com/t5/Desktop/Calculating-Cumulative-total-of-average-using-DAX-expressio...

Much thanks @v-frfei-msft

 

Regards,

Malav

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.