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

Please help! 

 

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

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

View solution in original post

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

Repeated Ids.PNG

 

Thanks in advance,

Kulchandra

 

 

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



 

Anonymous
Not applicable

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

Hi @Anonymous 

 

Plesae see the below.

 

image.png

 

Best Regards,
Mariusz

Please feel free to connect with me.
Mariusz Repczynski

 

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.  

 

Output.png

 

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

 

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.

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 Repczynski

 

Mariusz
Community Champion
Community Champion

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
Community Support
Community Support

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

Thank you so much! This worked!

Thejeswar
Resident Rockstar
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

Capture.PNG

 

 

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

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?

Hi @bhatsuchi,

Is this what you are looking for?

Capture.PNG

 

 

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

 

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

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

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.