Compute the Number of Interns Using Start Date and End Date and be able to Filter by Unit and Region

[Anonymous]

Hi People,

I am new to power BI. My issue is below: I want to be able to computer the number of interns present in the company using start date and end date and then also be able to filter by units, regions, internship_type, gender etc. Searching in this community, I found a measure able to do that as below (calculating the number present in a month):

number_of_interns_present_count = 
			
VAR endOfPeriod = MAX ( 'DimDate'[Date] ) 

VAR startOfPeriod = MIN( 'DimDate'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( 'internship interns' ),
        FILTER (
            ALL('internship interns'),
               ( 'internship interns'[effective_start_date] <= endOfPeriod
                         && 'internship interns'[effective_end_date] >= startOfPeriod)
        )
    )

However, my challenge now is that "number_of_interns_present_count" is same for all Units and Regions

Below is my model. Notice that I deactivated the relationship betweem DimDate and Interns effective start date thinking that was the problem but no change. When I am doing a simple count of interns and link DimDate to effective_start_date, I am able to get the number of interns starting in a particular month by Unit and Region when you select a month on the slicer. Please how can I filter by department, unit, gender, internship type etc for this case. For each of these filters the number of interns present is thesame through out. It is not splitted by any of the above.

[TeigeGao · Accepted solution]

Hi @Anonymous ,

The DAX query ypu shared can solve the problem computering the number of interns present in the company using start date and end date well. If you want to add the filters, you can change the ALL() function to ALLEXCEPT() function to rule out the units, regions, internship_type, gender.

Best Regards,

Teige

[Anonymous]

Hi @TeigeGao ,

Thanks alot. ALLEXCEPT works perfectly

Best Regards,

Divo

[Anonymous]

Hi @TeigeGao ,

The DAX query gives me the number of interns present correctly. However, my problem now is that it does not distribute the value correctly accross Units, Regions, Gender etc. The value is thesame. See visual below. How can I solve this?

[TeigeGao]

Hi @Anonymous ,

As mentioned in my above reply, you need to use allexcept() function to rule out these filters, because you areusing a all() function in your query, it will remove all filters, then it will display the same value.

Please refer to this blog: https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

Best Regards,

Teige

[Anonymous]

Hi @TeigeGao ,

Thanks. Let me try this and see the result

Best regards,

Divo