Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Afternoon,
Not sure if this is possible - I have about 20 columns in a dataset which contain either the value: Pass, Fail or N/A
I want to create a 'Total checked' column which will count across all 20 columns and sum the instances of Pass and Fail, ignoring N/A.
So in a single row, if 'Pass' in 2 columns, 'Fail' in 2 columns, 'N/A' in 2 columns, the new colum would result a 4.
Solved! Go to Solution.
Hi @Anonymous
You can do like this
First Step
Add an index column (From 0)
Final Step
add this custom column
List.Count( List.Select( Record.ToList(AddedIndex{[Index]}), each _ ="Pass" or _ ="Fail") )
AddedIndex refers to previousstep in your M code
Hi @Anonymous
You can do like this
First Step
Add an index column (From 0)
Final Step
add this custom column
List.Count( List.Select( Record.ToList(AddedIndex{[Index]}), each _ ="Pass" or _ ="Fail") )
AddedIndex refers to previousstep in your M code
Do you mind advising to my query, please?
https://community.powerbi.com/t5/Desktop/How-to-create-Clustered-Column-Chart-without-counting-Blank...
Thank you.
@Anonymous
Please see attached file's Query Editor with some sample data
@Zubair_Muhammad I have a second challenge for you.
Below is an excel equivalent of what I want to do in Power Query Editor with a new Custom Column - I want to create a [Fail Ratio] which takes the sum of Fails from the Total Checks to give a percentage. Easy.
Did that using [Total Fail]/[Total Checks]
But there are two elements - [Patient Name] and [Person to Pay] where if they fail, the [Fail Ratio] should be a complete fail, irrespective of whether these are only these two fails.
So I need the measure to give complete fail if [Patient Name] or [Person to Pay]="Fail", else [Total Fail]/[Total Checks]
@Anonymous
Sorry I missed your last reply
You can use a custom column like
=if List.Contains({[Patient Name],[Person To Pay]},"Fail") then 0 else [Total Fail]/[Total Checks]
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |