topic Re: How to Find Percentage Based on Another Field Containing a Specific String? in DAX Commands and Tips

How to Find Percentage Based on Another Field Containing a Specific String?

Asuna — Tue, 19 Sep 2023 13:03:15 GMT

Hello, I was looking for some help as I am quite new to DAX, I was wondering if it is possible to make a column that counts the total number of rows in Batch ID then count the number of instances in the notes column that contains a speific string? I am trying to make a percentage of Batch ID that contains a keyword in the notes. I only want to accomplish this because the batch id is unique and the notes are often duplicated and I need to be able to identify the percentages by method so just counting the notes will not work.

Re: How to Find Percentage Based on Another Field Containing a Specific String?

ERD — Tue, 19 Sep 2023 15:16:49 GMT

Hi. Please, provide some demo data and show the desired output.

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-Get-Your-Question-Answered-Quickly/td-p/1626726/jump-to/first-unread-message

Re: How to Find Percentage Based on Another Field Containing a Specific String?

Asuna — Wed, 20 Sep 2023 14:29:31 GMT

Oops, sorry here is some example data:
Batch Method Notes (String)

1	Method1	Correct 07 Jul 2023
2	Method1	Correct 11 Aug 2023
3	Method2	Correct 11 Aug 2023
4	Method1	Correct 19 Sep 2023
5	Method1	Error 19 Sep 2023

My desired output would be the ability to identify the rate at which the notes string contains the phrase error.

Method1	20% Error
Method2	0% Error

Re: How to Find Percentage Based on Another Field Containing a Specific String?

Anonymous — Wed, 20 Sep 2023 15:01:20 GMT

You need to do something like this. If this doesn't work, provide more context of your dataset. Happy to help.
Percentage =
VAR _CountRows =
    COUNTROWS ( 'Table' )
RETURN
    DIVIDE (
        _CountRows,
        CALCULATE (
            COUNT ( 'Table'[Method] ),
            ALL ( 'Table' )
        )
    )

Re: How to Find Percentage Based on Another Field Containing a Specific String?

Asuna — Wed, 20 Sep 2023 16:21:36 GMT

Apologies I think my first post was a bit confusing... here is an example of data for it.

And my desired output is a percentage based on the count of total rows in the batch column that has a note containing "Error" allowing you to sort by method as it is attached to the batch column which is always unique unlike notes.

Re: How to Find Percentage Based on Another Field Containing a Specific String?

ERD — Wed, 20 Sep 2023 16:55:44 GMT

So as far as I understand, Batch column contains unique values and you need pct per Method. Here is the measure:

pct = VAR method = SELECTEDVALUE ( Table_[Method] ) VAR errorRows = CALCULATE ( COUNT ( Table_[Batch] ), CONTAINSSTRING ( Table_[Notes], "Error" ), Table_[Method] = method ) VAR allRows = CALCULATE ( COUNT ( Table_[Batch] ), Table_[Method] = method ) RETURN DIVIDE ( errorRows, allRows, 0 )