Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I need help with DAX or custom column. I am following mandatory fields that my team needs to fill. So, if some field is missing, a table shows blank value on a cell.
Basically, I would like a card visual to show number of blank values in a whole dataset containing around 20 columns.
Sample data and table illustrated below:
So, totally 15 cells are blank, and need to be filled.
Additionally, I would like to create a visual that shows the number of blank values for each person.
Like:
James: 3
Jack: 1
Fernando: 3
Kate: 4
Robert: 4
Any suggestions how the problem could be solved? If more information is needed, don't hesitate to ask.
Thanks,
Jere Aallikko
Solved! Go to Solution.
You can take steps below for reference.
1. Go to Power Query Editor, you need to select all the columns, then
Get
2. Repeat the operation, and replace “List.NonNullCount” with “List.Count”
3. Add a custom column to get a diff, and change its type to Whole Number
4. Remove the two columns if you want
5. Create a measure
count = CALCULATE(SUM('Table'[Blank]),GROUPBY('Table','Table'[reporter]))
Hope it helps.
Best Regards,
Community Support Team _ Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
You can take steps below for reference.
1. Go to Power Query Editor, you need to select all the columns, then
Get
2. Repeat the operation, and replace “List.NonNullCount” with “List.Count”
3. Add a custom column to get a diff, and change its type to Whole Number
4. Remove the two columns if you want
5. Create a measure
count = CALCULATE(SUM('Table'[Blank]),GROUPBY('Table','Table'[reporter]))
Hope it helps.
Best Regards,
Community Support Team _ Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
@jereaallikko
You can add custom column using dax to the the table as follows:
Number of Blanks =
COUNTROWS(
FILTER( {TABLE02[ID], TABLE02[Amount], TABLE02[Created], TABLE02[Status], TABLE02[Department] ,
TABLE02[Product]}, [Value] = BLANK() )
)
Insert a card visual and include the newly created column, Use a Table visual to include Reporter Name and the new column to show by name.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |