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.
I wonder if the below is possible...
I have a data set like this:
Owner | Test Result | Test request date | Test result date |
Sally | Yes | 25/06/2020 | 27/06/2020 |
Sally | Yes | 15/06/2020 | 20/06/2020 |
Dave | No | 10/06/2020 | |
Dave | Not Applicable | ||
Sally | Not Applicable |
I am creating a report that monitors the number of blanks fields to ensure data is being entered.
If the test is not applicable, I don't want to count the blanks as they are not needed. However if the Test Result is Yes or No, there should be a date for the Request and the Result.
What would be the correct way to show this in a matrix as a count of the blanks? E.g. I'd like my matrix to look like...
Name | Blank Test Request Date | Blank Test Return Date |
Sally | 0 | 0 |
Dave | 0 | 1 |
Solved! Go to Solution.
Please try this
blank test request date =
CALCULATE(COUNTBLANK('Table'[Test request date]),FILTER('Table','Table'[test result]="Yes"||'Table'[test result]="No"))+0
blank test result date =
CALCULATE(COUNTBLANK('Table'[Test result date]),FILTER('Table','Table'[test result]="Yes"||'Table'[test result]="No"))+0
Proud to be a Super User!
Hi,
Can you try the below measure for counting test result blanks? The same needs to be created for other columns then use them.
BlankTestResultCheck =
CALCULATE (
COUNTROWS ( 'Table' ),
'Table'[Test Result] = "Yes"
|| 'Table'[Test Result] = "NO",
'Table'[Test result date] = BLANK ()
)
Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂
Regards,
N V Durga Prasad
Please try this
blank test request date =
CALCULATE(COUNTBLANK('Table'[Test request date]),FILTER('Table','Table'[test result]="Yes"||'Table'[test result]="No"))+0
blank test result date =
CALCULATE(COUNTBLANK('Table'[Test result date]),FILTER('Table','Table'[test result]="Yes"||'Table'[test result]="No"))+0
Proud to be a Super User!
That's brilliant, thanks for your reply.
For my own understanding can you tell me if '||' is what is needed to apply 2 filters? I had previously tried to add to filters (similar formula to your solution) but got a dax error in the measure.
|| means OR, that will only count staus is Yes or No and will not count the rows with Not applicable status.
Please let me know the error message.
Proud to be a Super User!
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 |
---|---|
113 | |
98 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |