cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## DAX to count rows with same value for Column A for a value in column B

```Id	Email
1	abc@gmail.com
2	xyz@gmail.com
3	jfr@gmail.com
1	abc@gmail.com
1	aBc@gmail.com
2	zdf@gmail.com
2	xyz@gmail.com
3	rtg@gmail.com```

Hello!

I have a table with data like above. I want to create a measure that can calculate total number of Ids with same value in the Email column.Should also ignore case for the email ids.  For the above example the total should be 2.(Ids 1 and 2)

I want another measure to calculate the total number of Ids with atleast 1 different email Id. For the above example the total should be 2.(Ids 2 and 3).

1 ACCEPTED SOLUTION
Community Support

You may refer to the measures below.

```Measure =
COUNTROWS (
FILTER (
VALUES ( Table1[Id] ),
CALCULATE ( COUNT ( Table1[Email] ) > DISTINCTCOUNT ( Table1[Email] ) )
)
)
```
```Measure 2 =
COUNTROWS (
FILTER (
VALUES ( Table1[Id] ),
CALCULATE ( DISTINCTCOUNT ( Table1[Email] ) > 1 )
)
)
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
16 REPLIES 16
Anonymous
Not applicable

@v-chuncz-msft  , @Thejeswar , @Mariusz , @Anonymous

I am new in powerbi and looking for solution that counts the repeated value in a column. Note: I ma using Direct query and not Import Mode.

```Repeated time         Count of Ids tha are repeating corresponds to (Repeated time) Column0 times         |      3
1 times         |      2
2 times         |      2
3 times         |      1```

Data i have is like this:

```ID_ColumnID_001ID_001
ID_002
ID_002
ID_002
ID_003
ID_003
ID_003
ID_004ID_004ID_005ID_006ID_007ID_008ID_008ID_008ID_008```

Please see the screenshot for deatils of my problem:

Kulchandra

Community Champion

Hi @Anonymous

You can create a table like below.

```Table =
DISTINCT( DirectQueryTable[ID_Column] ),
"Repeated time", FORMAT( COUNTROWS( DirectQueryTable ), "" ) & " Times"
)```
Later create relationship on DirectQueryTable[ID_Column] = Table[ID_Column] and create measure COUNTROWS( DirectQueryTable )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

Anonymous
Not applicable

@Mariusz  i am not sure how we create table out of query

Community Champion

Hi @Anonymous

Plesae see the below.

Best Regards,
Mariusz

Please feel free to connect with me.

Anonymous
Not applicable

@Mariusz Thank you for the quick reply. Table is created and relation also build between two columns. Between new table [id_column] and direct query table [id_column]. As per your instruction I have created another mesure also that counts rows for direct query table. But i didn't understand how to achive the result i wanted like i posted in my question.

Here is the result I am getting after used values from dynamic table created.
please find the sample PowerBi file (GoogleDrive). I have created this powerbi file using exact sample data i have provided in my quesion and applied your solution on that. If you can work on that file and send me back. That would be really helpful.

Community Champion

Hi @Anonymous

Sorry, missed CALCULATE()

```Table =
DISTINCT( DirectQuery[ID_ColumnID_001]),
"Repeated time", FORMAT( CALCULATE( COUNTROWS( DirectQuery ) ), "" ) & " Times"
)```

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

Anonymous
Not applicable

@Mariusz Great! I think one thing missing is. I am not seeing anything that is

0 Times repeated =

Is our dynamic table showing data like

ID_001 as  = 1 time repeated?

and

ID_001

ID_001

as =  2 times repeated?

After we got this. I would like to know if we can group repeated tiems like this example:

```0 times repeated       |    50 ids
1-3 times repeated     |    20 ids
4-5 times repeated     |    10 ids
>5 times repated       |     8 ids```

@Mariusz  Thank you so much for your help on this.

Community Champion

Hi @Anonymous

```Table =
SELECTCOLUMNS(
DISTINCT( DirectQuery[ID_ColumnID_001] ),
"no", CALCULATE( COUNTROWS( DirectQuery ) ) -1
),
"ID_ColumnID_001", [ID_ColumnID_001],
"Repeated time no", [no],
"Repeated time",
SWITCH(
TRUE(),
[no] = 0, "0",
[no] IN{ 1, 2, 3 }, "1-3",
[no] IN{ 4, 5 }, "4-5",
"> 5"
) & " times reported"
)```

Best Regards,
Mariusz

Please feel free to connect with me.

Community Champion

Hi @Anonymous

and - 1 to start @ 0 Times.

```Table =
DISTINCT( DirectQuery[ID_ColumnID_001] ),
"Repeated time", FORMAT( CALCULATE( COUNTROWS( DirectQuery ) ) -1, "" )  & " Times"
)```
Community Support

You may refer to the measures below.

```Measure =
COUNTROWS (
FILTER (
VALUES ( Table1[Id] ),
CALCULATE ( COUNT ( Table1[Email] ) > DISTINCTCOUNT ( Table1[Email] ) )
)
)
```
```Measure 2 =
COUNTROWS (
FILTER (
VALUES ( Table1[Id] ),
CALCULATE ( DISTINCTCOUNT ( Table1[Email] ) > 1 )
)
)
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

Thank you so much! This worked!

Resident Rockstar

Hi @bhatsuchi,

You can get the Count of Column B for each value in column A, by just setting the aggregation to Count in the visual where it is used

Not sure why you need a separate DAX for that? Clarify if anything..

Frequent Visitor

Thanks Thejeswar. This is if I had just these 2 fields. I have more fields along with these in which case it does not count appropriately. Sorry for not specifying on the existence of other fields too.

I need 2 measures though. Measure1- For all the Ids that appear more than once, I need a count of those that have the same email . Measure2 -For all the ids that appear more than once, a count of those that have atleast 1 different email.

There are Ids that appear just once with 1 email id. That should not be considered in the calculation.

Does this clarify?

Resident Rockstar

Hi @bhatsuchi,

Is this what you are looking for?

`Measure = IF(COUNT(Table1[Email])>0 && COUNT(Table1[Email])<=1,count(Table1[Email]))`
`Measure 2 = IF(COUNT(Table1[Email])>1,count(Table1[Email]))`

Frequent Visitor

Measure1 should have the value 2 (For Id=1 and Id=2)

Measure2 should have the value 2 ( For Id=2 and Id=3)

If I were to write a SQL to calculate Measure2 it would be -

SELECT count(distinct a.Id) FROM table1 a

JOIN table1 b on b.Id = a.Id AND (LOWER(b.[email] )<>LOWER( a.[email]))

Frequent Visitor

Measure1 should have the value 2 (For Id=1 and Id=2)

Measure2 should have the value 2 ( For Id=2 and Id=3)

If I were to write a SQL to calculate Measure2 it would be -

SELECT count(distinct a.Id) FROM table1 a

JOIN table1 b on b.Id = a.Id AND (LOWER(b.[email] )<>LOWER( a.[email]))

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.