Mapping aggregated data into a legend

[shill1000]

I haven't found an answer to this and I suspect it's impossible, but here goes.

I can't share the data as it's private, but essentially it’s a table of assets and a list of components and whether they are installed or not. In its basest form a device is good if it contains the component or bad if it doesn’t, but that good/bad value has to be an aggregate of installed components. I can use a measure to count how many assets are good and ho many are bad by aggregating the component install value; if all values = installed then good, else if any are not installed then bad. This gives a filterable result of a count of good and a count of bad assets.

The problem is the desire is to map the assets geographically and the legend is the good/bad value per device. As it’s a measure it can’t be used as the legend of the map. The only way I’ve found to get this to map is to group the asset data and use the grouping to aggregate the good/bad values on a new query, then use that query as a lookup back to the asset table to populate a new ‘compliance’ flag on each row.

Thus I can map the devices based on the aggregate state, but that map can’t be filtered by the component name because the device compliance is an aggregated value from the lookup. The desire is to say for a given list of compnents, flag a device good or bad, regardless of its overall state. Anyone got any ideas?

[Greg_Deckler]

If you could invent some data and post it here it would be extremely helpful. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490.

That being said, this sounds like a possible use case for the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

[shill1000]

Greg,

While I can't share actual data, the project is to set up a variant of the dashboard as per this thread:

https://community.powerbi.com/t5/Power-Query/System-Center-Configuration-Manager/m-p/267569

I've based the dashboard on the latter template linked by johnagillespie.

Basically, what I'm trying to do is replicate the compliance measures that are set up but, like the disconnected table suggestion, find a way to plot the devices on a map using a compliance flag as the legend. The need is for the map to be filterable by the existing filters in that template, as well as a visual level filter to only show a subset of records, Patient Zero (Name2 on CollectionMembership = Patient Zero).

As there is no geographical data stored in the SCCM database, I've added a lookup table for devices we know will be on the Patient Zero list and used a merge to populate the ComplianceInfo query with that data. 

The measures in the template are both using an aggregate count of entries that meet compliance rules (if all patches installed then compliant, else non-compliant) and I can’t use a measure in the legend. In trying to apply the equivalent logic using a disconnected table, it seems to accept the measures as per the example, but once I try to put the table field into the legend, it protests that it needs a relationship, which of course it can’t create from a disconnected table. I’m assuming this is because the other elements used in the map (geolocation data) are being pulled from the ComplianceInfo table and it can’t relate the legend to these values.

Any ideas?

[shill1000]

Thanks Greg, but that doesn't work, though it could be a mistake/misunderstanding on my part. It tries to force a relationship for the disconnected table.

I'll work on a mock up of the real data and post that, as I can't provide the real thing; security and it's on an on prem SQL server anyway.