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
bhrigu12
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
Anonymous
Not applicable

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
v-yuta-msft
Community Support
Community Support

@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

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.

Anonymous
Not applicable

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

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.