Forum Discussion

snoozee's avatar
snoozee
Regular Visitor
2 years ago
Solved

Weekly Check Calculation

Hi   I have a table that contains the following columns   Location, Date, CheckNotCompleted. WeekNumber and Year   My CheckNotCompleted column contains a Yes or No response   I need DAX to cr...
  • v-weiyan1-msft's avatar
    v-weiyan1-msft
    2 years ago

    Hi snoozee ,

     

    Based on the screenshot you provided, you need to change the code slightly as follows.

    Weekly Check = 
VAR CurrentLocation = 'Table'[Location]
VAR Cnt =
    CALCULATE (
        COUNT ( 'Table'[CheckNotCompleted] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[WeekNumber] ),
            'Table'[CheckNotCompleted] = "Yes"
                && 'Table'[Location] = CurrentLocation
        )
    )
RETURN
    IF ( Cnt >= 1, "Y", "N" )

    Result is as below.

    Hope that works for you.
    For further details，please find attachment.


    Best Regards,
    Yulia Yan


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-weiyan1-msft's avatar
v-weiyan1-msft
Community Support
2 years ago

Hi snoozee ,

 

Jihwan_Kim nice method! Thank you, for your helpful and quick response.

And you can also consider using the following code to create a Calculated column.

Weekly Check = 
VAR CurrentLocation = 'Table'[Location]
VAR Cnt =
    CALCULATE (
        COUNT ( 'Table'[CheckNotCompleted] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[WeekNumber] ),
            'Table'[CheckNotCompleted] = "Y"
                && 'Table'[Location] = CurrentLocation
        )
    )
RETURN
    IF ( Cnt >= 1, "Y", "N" )

My Sample:

Result is as below.

 

Best Regards,
Yulia Yan


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

snoozee's avatar
snoozee
Regular Visitor
2 years ago

Hi v-weiyan1-msft 

Thanks for getting back to me yet unfortunately it's not working as all of the results are 'N'  😣

The example below should be displaying 'Y' for all of these dates as a check was completed within the week.

 

Weekly Check =
VAR CurrentLocation = 'CameraDate'[Branch]
VAR Cnt =
CALCULATE (
COUNT ( 'CameraDate'[CheckNotCompleted] ),
FILTER (
ALLEXCEPT ( 'CameraDate', 'CameraDate'[WeekNumber] ),
'CameraDate'[CheckNotCompleted] = "Y"
&& 'CameraDate'[Branch] = CurrentLocation
)
)
RETURN
IF ( Cnt >= 1, "Y", "N" )

 

 

 
  • v-weiyan1-msft's avatar
    v-weiyan1-msft
    Community Support
    2 years ago

    Hi snoozee ,

     

    Based on the screenshot you provided, you need to change the code slightly as follows.

    Weekly Check = 
VAR CurrentLocation = 'Table'[Location]
VAR Cnt =
    CALCULATE (
        COUNT ( 'Table'[CheckNotCompleted] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[WeekNumber] ),
            'Table'[CheckNotCompleted] = "Yes"
                && 'Table'[Location] = CurrentLocation
        )
    )
RETURN
    IF ( Cnt >= 1, "Y", "N" )

    Result is as below.

    Hope that works for you.
    For further details，please find attachment.


    Best Regards,
    Yulia Yan


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • snoozee's avatar
      snoozee
      Regular Visitor
      2 years ago

      v-weiyan1-msft  Thank you so much for this and my apoligies as my brain obvioulsy wasn't working yesterday as i should have picked up the 'Y' rather the 'Yes' 😊