Object filter with display of unfilled and filled columns and values

[mmv]

Hello everyone,

I have a problem: I have a table that contains over 70 columns and over 10000 records.

My goal is to get a dashboard where I can filter by object name and display which columns are filled out for this object and what information is contained there.

I also want to display which columns are not filled out and preferably also display their amount.

For example:

Name	Data 1	Data 2	Data 3
a	1	4
b	2		3
c	3	1	4
d		2

If I filter by "a" I want to see something like this as a result:

Name	Filled columns	Value	Amount of unfilled columns	Unfilled columns
a	Data 1	1	1	Data 3
	Data 2	4

Not everything has to be displayed in a table, those could just as well be different visual elements.

How could this be solved?

Thank you for your help!

[Anonymous · Accepted solution]

Hi @mmv ,
Based on the data you provide, you can do the following:
Unpbviot  the data column

Create a table

Table 2 = VALUES('Table'[Attribute])

Create relationship

Create measures

Amount of unfilled columns = 
VAR allCount = 
CALCULATE(
    DISTINCTCOUNT('Table'[Attribute]),
    REMOVEFILTERS('Table'[Name])
)
VAR selected = DISTINCTCOUNT('Table'[Attribute])
RETURN
allCount - selected

Measure = 
IF(
    SELECTEDVALUE('Table'[Attribute]) IN VALUES('Table 2'[Attribute]),
    1,
    0
)

Create matrix

Create a table using table2 and apply a filter

Final output

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

[foodd]

Hello @mmv , and thank you for sharing a question with the Community.  This reply is informational.  Please remember to adhere to the decorum of the Community Forum when asking a question.

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot) and any Source files in Excel format (.XLSX). You can upload these files to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then share a file’s URL.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.  Proud to be a Super User!