COUNT Values From Measure

[HenryJS]

Hi all,

In the .pbix file below, is it possible to:

• Create a dropdown Filter for Missing/Approved (relating to calendar table)
• Create Counts of Missing / Approved (relating to calendar table)

Both counts and filters should be related to the other filters for dates and recruiters already in place.

Any help / advice would be greatly appreciated.

https://www.dropbox.com/s/sqq9o7xv9n3tcv3/Timesheet%20Tracker%20TEST.pbix?dl=0

Picture 1:

[v-zhenbw-msft]

Hi @HenryJS ,

How about the result after you follow the suggestions mentioned in my original post?

Could you please provide more details or expected result about it If it doesn't meet your requirement?

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.

[v-zhenbw-msft · Accepted solution]

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.

[Greg_Deckler]

In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...