Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Maprach
Regular Visitor

Pulling selected rows from a table based on multiple criteria

I deal with projects, that have many tasks. I maintain them in a table where there is a row for each task, and from the many columns for each task, the Status column is used to monitor progress (has values In Preparation, In Progress, Completed). One of the tasks for each project is called "Delivery". (Task name is stored in the TaskName column.) If the Delivery task has its Status set to "Completed", the Project is considered "Closed". (The other tasks, preceding in the workflow, would also be "Completed" by the time the "Delivery" task is.)

 

Each row with an indvidual task also has a column "Project" where information about the project is stored. By filtering for specific Project I can display all tasks belonging to it.

 

I need to create a new table containing all the tasks for Projects that are not yet "Closed", meaning their Delivery Task (the row with the Delivery task) has its Status set to "In Preparation" or "In Progress". In other words, while the original table has all the task rows for countless Closed projects, the new table shoul be much smaller, containing only all the task rows for Projects not yet closed.

 

Is it doable? What would be the best approach? Pulling out the rows from the original table based on the value "In Preparation" or "In Delivery" (Table.SelectRows) would not include all the tasks for active Projects, as some preceeding tasks might already be "Completed".

 

I think I first need to separately specify what Projects contain Delivery row with Status that is not Completed, and then in the next step pull into a new table all the rows (all the tasks) for these specified Projects.

 

I know I could use

= Table.SelectRows(BigTable, each ([Status] = "In Preparation" or [Status] = "In Progress") and ([TaskName] = "Delivery"))

 

to create a table listing just the rows for Not Completed "Delivery" Tasks. And the column "Project" in this table would list all the Project that are currently active (Not Yet Closed). But how do I now take this column, the unique Project numbers from this column, and tell Power BI to pull into a new table all task rows from the BigTable for any of these Project numbers?

 

This really goes beyond my newbie skills and it does not seem like I will grasp all of DAX and M in just one week. Would be grateful for any hints on how to achive this. Am out of ideas how to phrase this question for Google.

 

Thank you,

 

Maprach

1 ACCEPTED SOLUTION

@Maprach You should be able to use a Merge tables query to merge that column in the table with the Tasks table to get all of the tasks for those projects.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

@Maprach Why not just use a Table visualization instead of an actual table to display these? You could simply use the Filters pane to filter out task statuses that you don't want to display.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler Thank you, Greg. This table would be used in a Matrix Visual for a quick glance at where the project stands, with competed tasks shown in a different format than those not completed.  As I need to show all task statuses, I am not able to use filters on the visual. But I need to be showing this just for the projects that are active, as described above.

@Maprach You should be able to use a Merge tables query to merge that column in the table with the Tasks table to get all of the tasks for those projects.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler Yes, you are right, Merging does it. Thank you very much! I also found that if I connect these two tables, in the matrix visual I can list Projects from the Active Projects table and then add tasks from the big table, with their statuses, it also does the trick.  Not sure why I did not see that before. But you have helped greatly. Thank you.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.