COUNT Values From Measure

Hi HenryJS ,

We can use the following steps to meet your requirement.

1. Create a new table using Enter data.

2. Create two measures separately, one shows the Approved, another shows the Missing.

approved = 
VAR temp_table =
   FILTER (
        SUMMARIZE (
            'Timesheets export',
            'Timesheets export'[Period Ending],
            'Timesheets export'[Candidate Ref]
        ),
        'Timesheets export'[Candidate Ref]
            = MIN ( 'Export Placements'[CandidateRef] )
    )
VAR Active =
    IF (
        MAXX ( temp_table, 'Timesheets export'[Period Ending] )
            <= MAX ( 'Calendar'[Date] )
            && MAXX ( temp_table, 'Timesheets export'[Period Ending] )
                >= MAX ( 'Calendar'[Date] ),
        "Approved",
        BLANK()
    )
VAR Yes_value =
   IF (
        SELECTEDVALUE ( 'Export Placements'[PlacementStartDate] )
            <= MAX ( 'Calendar'[Date] )
            && SELECTEDVALUE ( 'Export Placements'[PlacementEndDate] )
                >= MAX ( 'Calendar'[Date] ),
        "Missing",
        ""
    )
RETURN
SWITCH ( TRUE (), Yes_value = "Missing" && Active = "Approved", Active, BLANK() )

Missing = 
VAR temp_table =
    FILTER (
        SUMMARIZE (
            'Timesheets export',
            'Timesheets export'[Period Ending],
            'Timesheets export'[Candidate Ref]
        ),
        'Timesheets export'[Candidate Ref]
            = SELECTEDVALUE ( 'Export Placements'[CandidateRef] )
    )
VAR Active =
    IF (
        MAXX ( temp_table, 'Timesheets export'[Period Ending] )
            <= MAX ( 'Calendar'[Date] )
            && MAXX ( temp_table, 'Timesheets export'[Period Ending] )
                >= MAX ( 'Calendar'[Date] ),
        "Approved",
        ""
    )
VAR Yes_value =
    IF (
        SELECTEDVALUE ( 'Export Placements'[PlacementStartDate] )
            <= MAX ( 'Calendar'[Date] )
            && SELECTEDVALUE ( 'Export Placements'[PlacementEndDate] )
                >= MAX ( 'Calendar'[Date] ),
        "Missing",
        ""
    )
RETURN
    SWITCH ( TRUE (), Yes_value = "Missing" && Active = "Approved", BLANK(), Yes_value )

3. Then we can create a measure in new table. Use this measure to replace the previous measure.

And add a slicer based on table[Status].

Measures to Show = 
IF(
                           HASONEVALUE('Export Placements'[Recruiter]),
                                    IF(HASONEVALUE('Table'[status]),
                                                SWITCH(
                                                VALUES('Table'[status]),
                                                "Approved",[approved],
                                                "Missing",[Missing]
                                                            ),
                                                [Timesheet?]
                                    )
)

4. We can create a measure to count the “missing” or “Approved”.

Count = 
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN (
                DISTINCT ( 'Calendar'[Week Ending] ),
                DISTINCT ( 'Export Placements'[Recruiter] ),
                DISTINCT ( 'Export Placements'[CandidateFirstName] )
            ),
            "Value", [Measures to Show]
        ),
        [Measures to Show] IN DISTINCT ( 'Table'[status] )
   )
)

Put it in matrix table, and the last result like this,

If you have any questions, please kindly ask here and we will try to resolve it.

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

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