Missing Project Name

[Jyaul1122]

Hello,

I have a table:

Data Date	Project
21-Jul-25	Project1
21-Jul-25	Project2
21-Jul-25	Project3
21-Jul-25	Project4
21-Jul-25	Project5
15-Aug-25	Project1
15-Aug-25	Project2
15-Aug-25	Project3
11-Sep-25	Project4
11-Sep-25	Project5
11-Sep-25	Project5

I need to find out missing project name for each Data Date, The total project is 5, if you will go by Data Date, Project count are :

Measures:

Project count = DISTINCTCOUNT('Table'[Project])

Total Project = CALCULATE(MAXX(VALUES('Table'[Data Date]),[Project count]),ALL('Table'[Data Date]))

Project Missing = [Project count] -CALCULATE(MAXX(VALUES('Table'[Data Date]),[Project count]),ALL('Table'[Data Date]))

, I need to know the Project missing  Name in each Data Date, For example: on 15_Aug there are two project missing (Project4 and Project5), On 11 - Sep there are three project missing ( Project1, Project2, Project3). How can I get missing project name using DAX as shown below in highlighted column:

[FBergamaschi]

Here is the solution, I created a simple data model with dimensuons Date and Projects connected 1 to many to the Facts table you supplied

Missing Projects =

VAR ALLPRojects = ALLNOBLANKROW( Project[Project ] )

VAR VisibleProjects = VALUES ( Facts[Project ] )

VAR MissingProjects = EXCEPT( ALLPRojects, VisibleProjects )

RETURN IF ( Project[project count] <> 0, CONCATENATEX( MissingProjects ,Project[Project ], " / " ))

PS I do not understand this code

Total Project = CALCULATE(MAXX(VALUES('Table'[Data Date]),[Project count]),ALL('Table'[Data Date]))

I think you can just write it as follows
Total Project = 

IF (

    [Project count] <>0 ,

    CALCULATE( [Project count], REMOVEFILTERS( 'Date' ) )

)

and Projects Minnsing Count becomes = [Total Project] - [Project Count]

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[Jyaul1122]

@FBergamaschi 

Thanks for your reply, its work fine but I have Project slicer on my page. Its need to be work as well selected project also. Please reply ?

[FBergamaschi]

What would be the purpose of that slicer? If you select let's say project1 and project2, what should be the effetct on the measure listing the missing ones?

Please show me an image of what you want to get and clarify the above question, thanks

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[Jyaul1122]

Hi @FBergamaschi ,

result:

Measures will also need to be work on selected Project only. For example: if i select Project 1, then result will be:

if I selected Project 1 and Project 5 then result will be: 

hope you understand my requirement . Thanks

[FBergamaschi · Accepted solution]

I understand

result without selection

result with selection

File available here

https://drive.google.com/drive/folders/1uQWkkvYJX4PRv0dZc-Y53z7_bi-Is2Ai?usp=sharing

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI