Getting Total Number of Projects by Department filtered from a Semantic Model

[YesP]

Hi,

I am trying to get Total Number projects from a Semantic Model with multiples Departments ID. I am filtering it for the Department IDs that I am working with. I am using the following Dax:

Number of Projects = CALCULATE(DISTINCTCOUNT(Project[Project ID]),ALLEXCEPT('Calendar',('Calendar'[Date])))

but it is bringing me the Total Projects of all organization.

The Semantic Model has data from all the organization and I am filtering it in Power BI for the Department ID that I need. So, the result that I want to get is Total Number of Project only of the Departments that I am filtering.

Please I will appreciate your help,

Thank you

[Anonymous]

Hi @YesP ,

Pls has your problem been solved? If so, accept the reply as a solution. This will make it easier for the future people to find the answer quickly.

If not, please provide a more detailed description, preferably some virtual sample data, and the expected results.

Best Regards,

Stephen Tao

[danextian]

hI @YesP 

Your filter modifier is applied to the date column in calendar table instead of to the department id from its own table so you still get total for the entire organization.

count by dept =
CALCULATE (
    DISTINCTCOUNT ( Project[Project ID] ),
    ALLEXCEPT ( department, department[department id] )
)

It removes all filters on the department table except for the department[department id] column. This ensures that the calculation respects the current department ID in the filter context but ignores filters applied to other columns in the table.

count by dept =
CALCULATE (
    DISTINCTCOUNT ( Project[Project ID] ),
    KEEPFILTERS ( department[department id] = "department id" )
)

This wil return the value for a specific department id. If a department id is selected other than what is in the measure, this will return blank.

count by dept =
CALCULATE (
    DISTINCTCOUNT ( Project[Project ID] ),
    department[department id] = "department id"
)

This will return the value for the deparment id indicated in the measure for all department ids. So if you add  deparment id to a table viz and the measure above, you'll get the same value for all rows.

[tharunkumarRTK]

@YesP 

I would suggest you to read the below articles to undestand how ALLEXCEPT function works.
https://dax.guide/allexcept/

https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

If you need further help, then please share your data model or expalin all the relationships in your semantic model

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[YesP]

I found that article before I posted my comment but it does not work for me. Thank you

[tharunkumarRTK]

@YesP 

Great! Share your data model. Without knowing the data model and visual context, it could be difficult for anyone to give you a solution based on assumptions. 

        How to Get Your Question Answered Quickly               

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!