Count unique cells with the same values

[Niunias]

Hello,

I am trying to figure out a way to count unique cells with  values. I merged 6 files and all of them have multiple sheets with the same layouts. It contains statuses of the tests, files are divided per country. I tried to build up some visualizations and measurments to show how many steps are already passed, failed for each country but I need check one more thing which is the purpose of my post.

Let me show you an example. Let assume that this a data from only two merged files :

Source Sheet TestScenario Result
 Spain      x               asd            fail
 Spain      x               qwe          pass
 Spain      x               rts             pass
 Italy        x               asd           pass
 Italy        x               qwe         pass
 Italy        x                rts           pass

I would like to measure how many results are pass and fail BUT if any of the same step is fail in other source, I would like to count it as fail.  As you can see Test Scenario "asd" in Spain is fail but in Italy is pass so I would like to count this step as fail.

I have 6 files so far with 993 test scenarios per file.  In the end I would like go through all of the steps  in all files and get result for instance : 500 pass, 493 fail in total, I dont want to count every steps in every source seperately/

Thank you

[Jihwan_Kim · Accepted solution]

Hi, @Niunias 

Please check the below picture and the sample pbix file's link down below.

All measures are in the sample pbix file.

https://www.dropbox.com/s/eoyy7prq9f3c3mv/niuniasv2.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Niunias]

Thank you, thank you so much 🙂