Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Count same value in some columns

Hi all,

i have some columns in my tables with Yes or No.

I would like to calculate the amount of Yes and No of alll them

 

A           B                C

Yes         Yes             No

No         No              No

No         Yes              Yes

 

anyone knows how to create the formula to calculate all "yes" values in just one measure??

2 ACCEPTED SOLUTIONS
Payeras_BI
Super User
Super User

Hi @Anonymous,

In PQ I would Unpivot the columns, filter by "Yes" and, then, Count Values.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WikwtVtKBkn75SrE60SBKB07ABRAKY2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type text}, {"C", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] = "Yes")),
    #"Calculated Count" = List.NonNullCount(#"Filtered Rows"[Value])
in
    #"Calculated Count"

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

View solution in original post

Hi @Anonymous ,

Try this instead:

Payeras_BI_1-1631602119421.png

let
    Origen = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WytNPPLRA4dACJR2lyNRiMFMBJgCUg5KxOtFKfvkYUn75KPIQM+AkWCIWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t]),
    #"Personalizada agregada" = Table.AddColumn(Origen, "# n/a", each List.Count(List.Select(Record.ToList(_), each _ =  "n/a")))
in
    #"Personalizada agregada"

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

View solution in original post

7 REPLIES 7
CNENFRNL
Community Champion
Community Champion

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WikwtVtKBkn75SrE60SBKB07ABRAKY2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t]),
    #"Count Yes" = List.Accumulate(Table.ToRows(Source), 0, (s,c) => s + List.Count(List.Select(c, each _ = "Yes")))
in
    #"Count Yes"

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Payeras_BI
Super User
Super User

Hi @Anonymous,

In PQ I would Unpivot the columns, filter by "Yes" and, then, Count Values.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WikwtVtKBkn75SrE60SBKB07ABRAKY2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type text}, {"C", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] = "Yes")),
    #"Calculated Count" = List.NonNullCount(#"Filtered Rows"[Value])
in
    #"Calculated Count"

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain
Anonymous
Not applicable

but, if i have about 50 columns of metadata and i want to count the number of yes in all of them, the upivot step will be a nightmare,

Hi @Anonymous ,

Could you provide some sample data?

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain
Anonymous
Not applicable

i have added an example table above.

 

A           B                C             D

n/a        Yes            n/a          n/a

No        n/a              No         n/a

No         Yes              Yes       n/a

 

I nned to count the number of n/a of a table, like the exmpale below but imagine in 50 colummns

and i need a formula to count them straigh away to the main table, without any traspose or similar

 

thanks

 

Hi @Anonymous ,

Try this instead:

Payeras_BI_1-1631602119421.png

let
    Origen = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WytNPPLRA4dACJR2lyNRiMFMBJgCUg5KxOtFKfvkYUn75KPIQM+AkWCIWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t]),
    #"Personalizada agregada" = Table.AddColumn(Origen, "# n/a", each List.Count(List.Select(Record.ToList(_), each _ =  "n/a")))
in
    #"Personalizada agregada"

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain
Anonymous
Not applicable

anyone can help?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors