cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Finding Pending List.

Hi everyone,

Can anyone help me with finding the pending list?? I have a fixed list of zones which needs to be audited every month. If any zone is not audited it should be shown as pending in BI dashboard. For example:

 

Table 1: (Fixed list)

Zone Name: (Header)

Zone A

Zone B

Zone C

 

Table 2: (Sharepoint- Data entered every month)

Audited Zone Name           Month         FY

Zone A                                March          19

Zone B                                APRIL           19

Zone A                                March          20

Zone C                                APRIL           20

 

 

Desired O/P list:

1). 

Pending Zone Name           Month         FY

Zone C                                March          19

Zone C                                APRIL           19

Zone C                                March          20

Zone B                                APRIL           20

 

OR

2).

Zone Name           Month         FY          STATUS

Zone A                  March          19         COMPLETED

Zone A                  APRIL           20         COMPLETED

Zone B                  March          19         COMPLETED

Zone C                  APRIL           20         COMPLETED

Zone C                  March          19         PENDING

Zone C                  APRIL           19         PENDING

Zone C                  March          20         PENDING

Zone B                  APRIL           20         PENDING

 

Can anyone help?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Re: Finding Pending List.

You say each zone gets audited every month, for every year.

I would approach this as then meaning you need to first have a 'blank' table of all the possible combinations.

This can be done purely in Power Query (I don't know if a DAX approach might be superior though).

 

You need to provide some more explanation as to the FY possibilities.  Can FY be defined as: FY <= (Current Year + 1)?  Knowing this can help to save some processing power.

 

We can somewhat automate the creation of this 'blank' table by first creating Years and Months tables.

Give your 'Zones', 'Months', and 'Years' tables a custom column = 1.  This will be used for left join purposes.

Create a reference to your 'Zones' table.  This is your 'blank' table.

 

In the 'Blank' Table, do a left join on the 'Years' Table, and then another left join on the 'Months' Table.  Use the 'Join Index' for the left joins.  Then expand the 'Years' Table, and then the 'Months' Table.

Now you have your 'Blank' Table.  Now do another left join with the 'Blank' Table and then the 'SharePoint Data' Table.  You'll need to select three columns from each table (ctrl + left click).  So select 'Zone', 'Year', and 'Month', in that order.  This will give you then a match for any values which exist in the SharePoint Data.  And then you can just do a simple conditional column to say: null = Pending, Audited Zone is equal to Zones column = Completed.  So at this point your 'Blank' Table has actually become your results.

 

Pbix file can be found here: https://1drv.ms/u/s!AgrPMBC9D01Ii3xCkymhyJ9ZeFs2

View solution in original post

3 REPLIES 3
Highlighted
Community Support
Community Support

Re: Finding Pending List.

@bhrigu12 ,

 


@bhrigu12 wrote:

I have a fixed list of zones which needs to be audited every month. If any zone is not audited it should be shown as pending in BI dashboard. 


The logic is not so clear. Could you share more details about the "not audited"?

 

Regards,

Jimmy Tao

Highlighted
Regular Visitor

Re: Finding Pending List.

Hi @v-yuta-msft ,

 

Thank you for your reply, let me clear your doubt. 

There is a standard checklist for all zones which needs to be filled (done by Powerapps) and data is stored in sharepoint. The auditor needs to fill this checklist for all zones every month. Suppose there are 10 zones. If for any zones the checklist is filled, it is reflected in Power BI dashboard. But I am not able to capture the zones which are not audited or pending in Power BI for each month. Suppose in one month out of 10 zones, only 08 is audited than this gets reflected but which 02 zones are pending is not getting reflected.

 

Hope I have cleared the doubt. Please reply if I need to explain more.

Highlighted
Frequent Visitor

Re: Finding Pending List.

You say each zone gets audited every month, for every year.

I would approach this as then meaning you need to first have a 'blank' table of all the possible combinations.

This can be done purely in Power Query (I don't know if a DAX approach might be superior though).

 

You need to provide some more explanation as to the FY possibilities.  Can FY be defined as: FY <= (Current Year + 1)?  Knowing this can help to save some processing power.

 

We can somewhat automate the creation of this 'blank' table by first creating Years and Months tables.

Give your 'Zones', 'Months', and 'Years' tables a custom column = 1.  This will be used for left join purposes.

Create a reference to your 'Zones' table.  This is your 'blank' table.

 

In the 'Blank' Table, do a left join on the 'Years' Table, and then another left join on the 'Months' Table.  Use the 'Join Index' for the left joins.  Then expand the 'Years' Table, and then the 'Months' Table.

Now you have your 'Blank' Table.  Now do another left join with the 'Blank' Table and then the 'SharePoint Data' Table.  You'll need to select three columns from each table (ctrl + left click).  So select 'Zone', 'Year', and 'Month', in that order.  This will give you then a match for any values which exist in the SharePoint Data.  And then you can just do a simple conditional column to say: null = Pending, Audited Zone is equal to Zones column = Completed.  So at this point your 'Blank' Table has actually become your results.

 

Pbix file can be found here: https://1drv.ms/u/s!AgrPMBC9D01Ii3xCkymhyJ9ZeFs2

View solution in original post

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors