Aggregate Data and visualize them

[MarcelStolzman]

Hi Fabric Community,

i have a problem creating a Report for Data Quality. Modelling in PowerQuery incl. relation is fine but i have no idea to aggregate the basedata in a form i need.

I have products and these products have properties. Some properties are mandatory and others are optional. All products have standard properties that are the same for everyone, e.g. manufacturer, type, year of manufacture. In addition, there is also specific data depending on the product type, e.g. cooling capacity for air conditioning systems. My report currently shows all products (IDs) individually and shows whether all mandatory properties are complete. Column 2 is the Count of mandatory properties for this product. Column 3 the count of maintained values. When column 4 is negative the product misses mandatory informations.

The Image is a Table Visual inPowerBI 

But instead I would like to show how many IDs are completely maintained and which IDs have missing data like 1000 IDs exist and 500 are fully maintained.

I'm unsure whether the summarization needs to be done in PowerQuery or instead using Dax in the visuals. I also have no idea how this aggregation of data works in PowerBI

Thanks in advance

[Anonymous · Accepted solution]

Hi @

Based on your description, do you want to know why in power BI visualization, why sometimes the aggregation displays the data and not all the data. This is because in power bi, it is the absolute image that automatically aggregates the data from the rows that are exactly the same in the columns that are being drained. This is to facilitate a clearer view of the data structure and to prevent redundant information from affecting the view. If you want to show all the data, you need to have a column so that each row has a unique value. You can do this by adding an indexed column to the power query. And place this indexed column on the visualization.
If you get the number of rows that are not fully visualized, you can refer to the following expression:

Count Duplicate = 
VAR a = COUNTROWS('Table')
VAR b = 
SUMMARIZE(
	'Table',
	'Table'[Key],
	'Table'[Value]
)
RETURN
a - COUNTX(b,'Table'[Key])

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[MarcelStolzman]

The Datamodel has 5 relevant tables.

Table1 Products
Table2 Productyps
Table3 Properties
Table4 PropertyAssignment depending on producttype, here is information saved wether this property is mandatory for this product or not,

Table5 PropertyValues with ProductIDs, Property an PropertyValue

i have combined these table in a query with all products, producttypes, the properties which are assigned for the product (depending on PropertyAssignment) and is the property mandantory or not and last but not least the value of the property for this product. If the value is blank, the property isn't maintained