Forum Discussion

luisfc's avatar
luisfc
New Member
2 years ago

Listing distinct values on a filtered table

[edited for clarity]

 

I have been trying to achieve this for the last 3+ weeks. Youtube and forums have been searched through and throuhg and I haven't found an answer.

The single table I have contains Tasks, Status [Backlog|Done|Ongoing], Resolved date, associate Project and associated Program.

Programs can have 1:multiple projects and projects can have 1:multiple tasks. This is a reflection of our team's Kanban project methodology.

 

My intention is to list Programs that have tasks resolved within a given time frame, the count of other tasks that link to the same program, for each status: Done | Backlog | Ongoing.

Using a date slicer does not work as the Resolved date is empty for Status=Backlog & Ongoing, so I am using a measure and an aux Date table to filter tasks:

 

Initiative touched in the period =
var _from = FIRSTDATE('Date'[Date])
var _to = LASTDATE('Date'[Date])
return
if (SELECTEDVALUE('test data'[Resolved]) >= _from && SELECTEDVALUE('test data'[Resolved]) <= _to, 1, 0)
 

Results can then be captured in this visual table:

 

Now as I want to display the distinct Programs, I remove the other columns, keeping the filter:

This is not the result I need. It clealry misses the other rows: CIS-201, CIS-203, CIS-206,...

 

Thank you in advance for your time on this!

See attached pbix here

 

 

Need Help
Tips and Tricks

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    luisfc , You need to count the project have all tasks finished on time ? Assume you have measures for the resolve time and standard time (or use static value)

    Try measures like

     

     

    On Time Task = countx(Values(Table[Task]),if([resolve time] <=[Std Time], [Task], blank() ) )

     

     

    Total Task = countrows(Values(Table[Task]))


    On Time projects = countx(Values(Table[Project]),if([On Time Task] =[Total Task], [Project], blank()))