Summarising state in another table?

[haddrick]

Hi,

 Brand new PowerBI Desktop user here. I have a specific application I am trying to put together. Below is the raw data sample:

ApplicationUsage

Unique combinations of App ID, Username, Site

SiteList

All sites, unique

ApplicationReadiness

App ID, App Readiness State

Essentially, App Readiness should dictate a users readiness, and therefore the site's readiness. This should provide visibility on what sites are ready and not ready, and then later on what apps are blocking sites and which are blocking more than others.

I currently have a calculated column in ApplicationUsage from App Readiness, so the unique App/user/site also has readiness.

I am looking to set up a few views from here and am very stuck:

1. Site | Readiness
   • Readiness should be a AND() function of all entries in ApplicationUsage, so if all entries for the site are ready then the site is ready, otherwise it is not ready.
   • I am having trouble seeing how this should 'roll-up' status and how best to do ths in PowerBI, since site is unique.
   • I'm currently trying to do this with a calculated column in the Sites table, but am having trouble with this.
2. Site (Not ready) | Last app readiness date
   • For blocked sites, what is blocking sites, and how can I schedule them
   • Need to have the first view to do this
3. Application (not ready) | Blocked sites | user #
   • For unfinished applications, which are more impactful than others

Thanks for your help, I'm sure this is relatively simple for more experienced users. If there are resource for this sort of approach I'd be very interested to find out

[Habib · Accepted solution]

Here is the dataset I have used.

While connecting to PowerBI, relationship was created between Site & User and Application & User tables.

Now I created a new column in Application table with name "Status" with following formula

Status = IF('Application Status'[Readiness]=TRUE(),0,1)

Next I added a new measure in Site table with name "SiteStatus" with following formula.

Site Status = CALCULATE(SUM('Application Status'[Status]),GROUPBY(Site,Site[Site]))

Next I added a new measure in site table with name "Completion Date" having following formula

Completion Date = CALCULATE(MAX('Application Status'[ETA]),GROUPBY(site,Site[Site]))

Now we are ready to use these measure to display your required information on report. Select Table visual and select Site to be displayed on it. Apply filter on Site Status = 0. This will display sites which are with completed status. Refer to below image.

Now place another Table visual and select site along with Completion date. Add site status to filter and apply stuatus greater than 0. Please refer to below image.

Hope this will help you to achive what you want.