topic Re: How to compare row by row text field with other fields to check similarity in Developer

How to compare row by row text field with other fields to check similarity

Anonymous — Fri, 02 Jun 2023 09:25:09 GMT

Hello Experts,

I am new to Power BI and stuck with one of my requirement. Below is my simplified table

resourcename | servertype | start_date | Start_Time |
Abc dbo 06-06-2023 1:00:00 AM
DBA dbo 06-06-2023 1:00:00 AM
CDS ora 04-06-2023 3:00:00 AM

Below table is the requirement is to find concurrency issue, i.e. 2 diferrent resourcename should not have same date, same time & same servertype. So, in first 2 rows we can see the resourcename are differrent but rest all is same. So expectation is that it should show 2 counts for 06-06-2023 as concurrency issue

Output expected:

Start_date | Concurrent issue |

06-06-2023 2

Could someone please suggest how can we derive this using DAX or Query Editor?
NOTE: All fields are "text" datatype. If required I can change the datatype for date & time

Thanks in Advance

Re: How to compare row by row text field with other fields to check similarity

lbendlin — Sat, 03 Jun 2023 22:55:58 GMT

There is no magic formula. You would need to implement all checks manually, using SUMMARIZE and COUNTROWS. OR - simply plop your data into a matrix visual and present that as a graphical solution to the questions.

If you want to be cute you can run a Pearson correlation across your data rows but that is better done outside of Power BI.

Re: How to compare row by row text field with other fields to check similarity

Anonymous — Mon, 05 Jun 2023 08:08:37 GMT

Thanks @lbendlin , Thanks for your suggestion. That helped me. I used the below formula and it worked for me

calculate(distinctcount(Table[resourcename]), ALLEXCEPT(Table, Table[servertype], Table[start_date], Table[Start_Time])