topic Re: count values in column A only when all corresponding column B values are same in DAX Commands and Tips

count values in column A only when all corresponding column B values are same

Tamillllll — Mon, 22 Jan 2024 04:56:13 GMT

A		B
1893492		3740100990
1893492		3740100990
1893492		3740100990
1893492		3740100990
1893492		-1
1902593		3740100991
1902593		3740100991
1902593		3740100991
1902593		-1
1905279		-1
1905279		-1
1905279		-1
1905279		-1
1905279		-1
1905279		-1
1911152		-1
1911152		-1
1911152		-1
1911152		-1
1911152		-1
1911152		-1
1911152		-1

Hi,
I wanted to distinct count values in A only when all corresponding values are -1 in B.
eg: in above table, 1905279 and 1911152 ate two values which has all its corresponding B values as -1.
ths output is 2 for this.

Any help is appreciated.

Thanks,

Re: count values in column A only when all corresponding column B values are same

lbendlin — Mon, 22 Jan 2024 19:18:05 GMT

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrSwNDaxNFLSUTI2NzEwNDCwtDRQitWhkYSuIUTA0sDI1NIYWSXlEgizTY3MLWkuYGhoaGpEV4FYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"A"}, {{"CountD", each Table.RowCount(Table.Distinct(_)), Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([CountD] = 1)) in #"Filtered Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

Re: count values in column A only when all corresponding column B values are same

Tamillllll — Mon, 22 Jan 2024 22:59:14 GMT

Thank you so much for the solution, however its giving count of distinct values in B. I want to distinctcount column A when all its values is -1. As provided above in dax.

Re: count values in column A only when all corresponding column B values are same

Anonymous — Tue, 23 Jan 2024 02:35:17 GMT

Hi, Tamillllll

May I ask if this is the expected output you are looking for? Based on your description, I have created a new Table to achieve the effect you are looking for. Following picture shows the effect of the display.

New Table:

UniqueA = SUMMARIZE ( FILTER ( ADDCOLUMNS ( SUMMARIZE ( 'Table', 'Table'[A], "UniqueValuesInB", DISTINCTCOUNT ( 'Table'[B] ) ), "IsSingleValueInB", [UniqueValuesInB] = 1 ), [IsSingleValueInB] ), [A] )

If this does not work, could you please share some sample data without sensitive information and expected output.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data in the Power BI Forum

Re: count values in column A only when all corresponding column B values are same

Tamillllll — Tue, 23 Jan 2024 04:07:35 GMT

Hi yang,

Thank you sp much for the solution, I have explained in the below screenshot and also provided data below.

Data:

JourneyID	Stop Sequence	Vehicle Id	Route
1893494	1	3740000990	661
1893494	2	3740000990	661
1893494	3	3740000990	661
1893494	4	3740000990	661
1893494	5	3740000990	661
1893494	6	3740000990	661
1893494	7	-1	661
1902590	1	3740000990	661
1902590	2	3740000990	661
1902590	3	3740000990	661
1902590	4	3740000990	661
1905277	1	-1	661
1905277	2	-1	661
1905277	3	-1	661
1905277	4	-1	661
1905277	5	-1	661
1905277	6	-1	661
1911151	1	-1	661
1911151	2	-1	661
1911151	3	-1	661
1911151	4	-1	661
1911151	5	-1	661
1911151	6	-1	661

Re: count values in column A only when all corresponding column B values are same

talespin — Tue, 23 Jan 2024 08:10:01 GMT

I am a newbie, It can be optimized further. Put it in a measure.

Just check, using Power Query might be a better solution, you have to compare performance.

//Grouping table by column A and B

Count =

VAR groupTable =

SUMMARIZE(

TestTable,

TestTable[A],

TestTable[B]

)

//Grouping the previous table by Column A only and taking count

VAR countColA =

GROUPBY(

groupTable,

[A],

"CountA",

COUNTX(CURRENTGROUP(),[A])

)

//filtering table to return only values that have count of 1

VAR filterTable = FILTER(

countColA,

[CountA] = 1

)

//returning total count, which is 2 in sample data shared by you.

return COUNTX(filterTable, [A])

Re: count values in column A only when all corresponding column B values are same

Tamillllll — Wed, 24 Jan 2024 03:50:37 GMT

Thanks talespin , it gives single occurrences of unique combinations of A and B.

Re: count values in column A only when all corresponding column B values are same

talespin — Wed, 24 Jan 2024 05:49:20 GMT

Tamillllll

Apology, I did not check for all B values = -1

//Grouping data by A and B

UniqueCountA =

VAR groupTable =

SUMMARIZE(

TestTable,

TestTable[A],

TestTable[B]

)

//Adding column with count of A for each value of A

VAR countColA =

ADDCOLUMNS(

groupTable, "CountA",

VAR _valA = [A]

RETURN CALCULATE(COUNTX( FILTER(groupTable, [A] = _valA), [A]))

)

//Checking for B = -1 and Count = 1, we want records only having rowcount of 1

VAR filterTable = FILTER(

countColA,

[B] = -1 && [CountA] = 1

)

return COUNTX(filterTable, [A])