Forum Discussion

MVCPA's avatar
MVCPA
Frequent Visitor
10 months ago
Solved

Help with DAX Measure - Sum with condition

Hi all!,   I have table that contains a list of employees and their timesheet entries. Currently, my manager wants to apply a logic and show it up in a Matrix.   Logic: IF Employee A's all hours ...
Need Help
Tutorial Requests
  • v-sathmakuri's avatar
    v-sathmakuri
    9 months ago

    Hi MVCPA ,

     

    Sorry for the delay in response!!

     

    Below is the updated measure which is showing normal time only for indirect for karen employee. Attached pbix file for reference.

     

    Expected Value =
    SUMX(
        SUMMARIZECOLUMNS(
            employee[crc27_employee],
            'Time type'[crc27_timetype]
        ),
        VAR CurrentHours =
            CALCULATE(
                SUM('Sample'[crc27_hours])
            )
        VAR HasData =
            NOT ISBLANK(CurrentHours)
        VAR NonNormalHours =
            CALCULATE(
                SUM('Sample'[crc27_hours]),
                REMOVEFILTERS('Time type'),
                NOT('Time type'[crc27_timetype] IN {"Normal Time"})
            )
        RETURN
            IF(
                HasData,
                IF(
                    NonNormalHours >= 164.67,
                    SWITCH(
                        TRUE(),
                        SELECTEDVALUE('Time type'[crc27_timetype]) = "Normal Time", 0,
                        CurrentHours
                    ),
                    SWITCH(
                        TRUE(),
                        SELECTEDVALUE('Time type'[crc27_timetype]) = "Normal Time",
                        ABS(NonNormalHours - CurrentHours),
                        CurrentHours
                    )
                ),
                BLANK()
            )
    )
     
     

    Please review the output and let us know if you still need any further assistance here.

     

    Thanks & Regards,

    Rekha Athmakuri.

Jihwan_Kim's avatar
Jihwan_Kim
Super User
10 months ago

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the  below picture and the attached pbix file.

 

 

 

 

 

 

expected result measure: = 
SUMX (
    SUMMARIZECOLUMNS ( employee[employee], hours_type[hour_type] ),
    CALCULATE (
        IF (
            CALCULATE (
                SUM ( hours[hours] ),
                REMOVEFILTERS ( hours_type ),
                NOT ( hours_type[hour_type] IN { "Normal Time" } )
            ) >= 164,
            SWITCH (
                TRUE (),
                SELECTEDVALUE ( hours[hour_type] ) = "Normal Time", 0,
                SUM ( hours[hours] )
            ),
            SWITCH (
                TRUE (),
                SELECTEDVALUE ( hours[hour_type] ) = "Normal Time",
                    ABS (
                        CALCULATE (
                            SUM ( hours[hours] ),
                            REMOVEFILTERS ( hours_type ),
                            NOT ( hours_type[hour_type] IN { "Normal Time" } )
                        )
                            - SUM ( hours[hours] )
                    ),
                SUM ( hours[hours] )
            )
        )
    )
)