Forum Discussion
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
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.
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