cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sun_SACHA
Frequent Visitor

List members not assigned to a project for a given date range

I have 3 tables, Projects, Members and Member Assignment as below.

 

Projects

ProjectStart DateEnd date
Proj110 January 202013 March 2020
Proj212 February 202030 April 2020
Proj301 January 202022 May 2020

 

Members

Member1
Member2
Member3
Member4
Member5
Member6

 

Member Assignement

Member Project Start DateEnd date
Member1 Proj1 15 January 202030 January 2020
Member2 Proj1 10 January 202013 March 2020
Member1 Proj1 01 March 202013 March 2020
Member3 Proj102 January 202012 February 2020
Member4 Proj125 February 202005 March 2020
Member1 Proj212 February 202030 April 2020
Member3 Proj201 April 202015 April 2020
Member4 Proj301 January 202022 May 2020

 

How can I list members who are NOT assigned in any project for a given calendar Start and End Date.

For eg, Member assignment Start Date 01 March 2020  to End Date 13th March 2020

 

Member 6No assignment at all
Member 5No assignment at all
Member 3doesnt have assignment during (1-Mar-20 to 13-Mar-20)
Member 4 Partial overlap for 5 days from 1st to 15th if it can be differentiated even better

  

Kind regards,

Sunish

1 ACCEPTED SOLUTION

Hi, @sun_SACHA 

 

You may use visual level filter to control the display of the visual.

b1.png

 

Best Regards

Allan

 

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

 

View solution in original post

5 REPLIES 5
v-alq-msft
Community Support
Community Support

Hi, @sun_SACHA 



You may create a calculated table and a measure like below. The pbix file is attached in the end.

 

Calculated table:
Calendar = CALENDARAUTO()

Measure:
Result = 
var cmember = SELECTEDVALUE('Members'[Members])
var _mindate = 
CALCULATE(
    MIN('Calendar'[Date]),
    ALLSELECTED('Calendar')
)
var _maxdate = 
CALCULATE(
    MAX('Calendar'[Date]),
    ALLSELECTED('Calendar')
)
return
IF(
    NOT(cmember in 
    CALCULATETABLE(
        DISTINCT('Member Assignment'[Member]),
        ALL('Member Assignment')
    )),
    "No assignment at all",
        IF(
            SUMX(
                FILTER(
                    SUMMARIZE(
                        ALL('Member Assignment'),
                        'Member Assignment'[Member],
                        'Member Assignment'[Project],
                        'Member Assignment'[Start Date],
                        'Member Assignment'[End Date],
                        "flag",
                        IF(
                            NOT(
                                OR(
                                   'Member Assignment'[Start Date]>_maxdate,
                                   'Member Assignment'[End Date]<_mindate
                                )
                            ),
                            1,0
                        )
                    ),
                    [Member]=cmember
                ),
                [flag]
            )>0,
            "have assignment during "&_mindate&" to "&_maxdate,
            IF(
                SUMX(
                    FILTER(
                        SUMMARIZE(
                            ALL('Member Assignment'),
                            'Member Assignment'[Member],
                            'Member Assignment'[Project],
                            'Member Assignment'[Start Date],
                            'Member Assignment'[End Date],
                            "flag",
                            IF(
                                'Member Assignment'[Start Date]>_maxdate||'Member Assignment'[End Date]<_mindate,
                                1,0
                            )
                        ),
                        [Member]=cmember
                    ),
                    [flag]
                )>0,
             "doesnt have assignment during "&_mindate&" to "&_maxdate
        )
    )
)

 

 

Result:

h1.png

 

Best Regards

Allan

 

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

Thanks you @v-alq-msft Allan for your effort in giving the solution.

 

The answer gave me an idea on how to do what's required, but would appreciate an exact solution, which is a listing of all members(Only member names needed) who ARE NOT assigned to any project given any date range or project selection filter.

 

For eg If I select  Proj1 as per your pbix, only member4,6 should be listed, for Proj 2, Members2,4,5,6  and for Proj3 Members 1,2,3,5,6 

 

Similarly for any date range, only memebers who are unassigned during that period should be listed. May be I was not very clear on my question adding to the ambiguity with the scenario where there is a partial period assignmenet. 

 

 

Hi, @sun_SACHA 

 

You may use visual level filter to control the display of the visual.

b1.png

 

Best Regards

Allan

 

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

 

I modified the Measure to just show as Assigned/Unassigned for all scenarios and by the using filter on visual Iam able to filter Unassigned.

 

Is it possible to have a slicer for Unassigned ? 

 

 

 

Hi, @sun_SACHA 

 

A measure is evaluated in the context of the cell of the pivot table or DAX query. You can't put a measure in a slicer which filters all the visual in a report page.

 

Best Regards

Allan

 

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.