Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Solved! Go to Solution.
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 ) ) )
@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
Hi @Anonymous
You can create a table like below.
Table = ADDCOLUMNS( DISTINCT( DirectQueryTable[ID_Column] ), "Repeated time", FORMAT( COUNTROWS( DirectQueryTable ), "" ) & " Times" )
@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.
Hi @Anonymous
Sorry, missed CALCULATE()
Table = ADDCOLUMNS( DISTINCT( DirectQuery[ID_ColumnID_001]), "Repeated time", FORMAT( CALCULATE( COUNTROWS( DirectQuery ) ), "" ) & " Times" )
@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" )
Hi @Anonymous
and - 1 to start @ 0 Times.
Table = ADDCOLUMNS( DISTINCT( DirectQuery[ID_ColumnID_001] ), "Repeated time", FORMAT( CALCULATE( COUNTROWS( DirectQuery ) ) -1, "" ) & " Times" )
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 ) ) )
Thank you so much! This worked!
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..
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?
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]))
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
84 | |
84 | |
72 | |
49 |
User | Count |
---|---|
143 | |
131 | |
109 | |
64 | |
55 |