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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Jessie666
Frequent Visitor

Employee month level movement status for Dropped off, New added, and No change.

Hi everyone,

 

I'm working on a DAX to find out the employee movement status for "Dropped Off", "New this month", and "No change" for two pay months. My Dax somehow return everyone in paymonth 3 as Dropped off and everyone in paymonth 4 as New this month.  Can someone help me to fix my DAX? The goal is to add a new column to show the status changes so I can use it as a filter (measurement can't be filter). I also tried to do it in the query editor but it was harder than DAX. 

 

Current code I have: 

Emp Status =
VAR TotalPersonsWithPaymonth3 =
    CALCULATE (
        DISTINCTCOUNT ( Sheet1[PERSON_ID]),
        Sheet1[PayMonth] = "3"
    )
VAR TotalPersonsWithPaymonth4 =
    CALCULATE (
        DISTINCTCOUNT ( Sheet1[PERSON_ID] ),
        Sheet1[PayMonth] = "4"
    )
RETURN
    IF (
        TotalPersonsWithPaymonth3 > 0 && TotalPersonsWithPaymonth4 = 0,
        "Dropped Off",
        IF (
            TotalPersonsWithPaymonth3 = 0 && TotalPersonsWithPaymonth4 > 0,
            "New This Month",
           IF (
                TotalPersonsWithPaymonth3 > 0 && TotalPersonsWithPaymonth4 > 0,
                "No Change",
                BLANK ()
        )
       
    )
)
 
I appreciate your help!!
2 REPLIES 2
Sahir_Maharaj
Super User
Super User

Hello @Jessie666,

 

Can you please try:

Emp Status =
VAR TotalPersonsWithPaymonth3 =
    CALCULATE (
        DISTINCTCOUNT ( Sheet1[PERSON_ID] ),
        Sheet1[PayMonth] = "3"
    )
VAR TotalPersonsWithPaymonth4 =
    CALCULATE (
        DISTINCTCOUNT ( Sheet1[PERSON_ID] ),
        Sheet1[PayMonth] = "4"
    )
RETURN
    IF (
        TotalPersonsWithPaymonth3 > 0 && TotalPersonsWithPaymonth4 = 0,
        "Dropped Off",
        IF (
            TotalPersonsWithPaymonth3 = 0 && TotalPersonsWithPaymonth4 > 0,
            "New This Month",
            IF (
                TotalPersonsWithPaymonth3 > 0 && TotalPersonsWithPaymonth4 > 0,
                "No Change",
                BLANK ()
            )
        )
    )

Let me know if you might require any further assistance.


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Hi  Sahir,

 

Thank you very much for looking into my issue. The code you sent works the same like the one I had in my post. It can't determine who stayed "No Change" I think this is because for DAX expression only look at the person_id in the row, not everyone in the whole table. If I put this as a measure and in a visual, it will look all of the selected Person_ID in the visual. So, in order to add a new column for status it will need to do in the power query editor. But I haven't figured out how to do so. Please let me know if you know how. I appreciate your help!

 

Best regards,

Yiyi

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.