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

[bhatsuchi]

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).

Please help! 

[Anonymous]

@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) Column
0 times         |      3 
1 times         |      2
2 times         |      2
3 times         |      1

Data i have is like this:

ID_ColumnID_001
ID_001
ID_002
ID_002
ID_002
ID_003
ID_003
ID_003
ID_004
ID_004
ID_005
ID_006
ID_007
ID_008
ID_008
ID_008
ID_008

Please see the screenshot for deatils of my problem:

Thanks in advance,

Kulchandra

[Mariusz]

Hi @Anonymous 

You can create a table like below.

Table = 
ADDCOLUMNS(
    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]

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

[Mariusz]

Hi @Anonymous 

Plesae see the below.

Best Regards,
Mariusz

Please feel free to connect with me.

[Anonymous]

@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.  

[Mariusz]

Hi @Anonymous 

Sorry, missed CALCULATE()

Table = 
ADDCOLUMNS(
    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]

@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.

[Mariusz]

Hi @Anonymous 

Please see the adjusted code.

Table = 
SELECTCOLUMNS( 
    ADDCOLUMNS(
        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.

[Mariusz]

Hi @Anonymous 

and - 1 to start @ 0 Times.

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

[v-chuncz-msft · Accepted solution]

@bhatsuchi,

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 )
    )
)

[bhatsuchi]

Thank you so much! This worked!

[Thejeswar]

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..

[bhatsuchi]

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?

[Thejeswar]

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]))

[bhatsuchi]

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]))

[bhatsuchi]

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]))