Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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]))
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
91 | |
88 | |
35 | |
35 |
User | Count |
---|---|
153 | |
99 | |
82 | |
63 | |
54 |