topic Count unique cells with the same values in DAX Commands and Tips

Count unique cells with the same values

Niunias — Mon, 31 May 2021 07:56:22 GMT

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

Re: Count unique cells with the same values

Jihwan_Kim — Mon, 31 May 2021 08:24:14 GMT

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

Re: Count unique cells with the same values

Niunias — Mon, 31 May 2021 09:41:25 GMT

Thank you, thank you so much 🙂