Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Abhi_1992
New Member

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

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

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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.

View solution in original post

2 REPLIES 2
Abhi_1992
New Member

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

lbendlin
Super User
Super User

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.