Forum Discussion

Jyaul1122's avatar
Jyaul1122
Helper III
1 year ago
Solved

Missing Project Name

Hello,

I have a table:

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

 

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:
 

 

 

 

 

 

Need Help

5 Replies

  • FBergamaschi's avatar
    FBergamaschi
    Super User
    1 year ago

    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's avatar
      Jyaul1122
      Helper III
      1 year ago

      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's avatar
        FBergamaschi
        Super User
        1 year ago

        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