Please help with counting a very tricky one

[mcgeeks]

Greetings,

• I am tasked with counting 2 columns, Sample and Test, with millions of rows. 
• Items in both columns can be repeated and typically are. 
• I already gather a distinct count of the Sample column, so that's fine. 
• So now, I'm trying to gather how many test names are in the test column per sample in the sample column.  If I pull distinct values from the test column it's only 11 tests.  If I pull non-distinct values I get many more than I should. 

Then comes the really tricky part.  The Tests can all be ran individually.   However, sometimes Test 1, Test 2, and Test 3 need to be tied together for a sample and need to be counted as 1 Test.  I've tried grouping "Tests" in PowerBI to achieve that, but that doesn't seem to help.

Any ideas anyone?  Thanks in advance.

Please see the example below.

Sample	Test
A	TEST 1	Sample A=2 test names
A	TEST 1
A	TEST 1
A	TEST 2
A	TEST 2
B	Test 1	Sample B=3 test  names
B	Test 2
B	Test 3
C	TEST 1	Sample C=2 test names
C	TEST 2	(1,2,3 as 1)
C	TEST 3
C	Test 1
C	Test 1
C	Test 1
C	Test 1

[Anonymous]

Hi, @mcgeeks 

According to your description, if the Test columns are different and the Sample columns have the same data, then the corresponding rows are output, there are a few questions:
1.Sample C=2 test names how do you get this data, (1,2,3 as 1) means if TEST 1, TEST 2, TEST 3 all appear then it is only seen as TEST 1?
2. you eventually want to realize the effect in the matrix, you can share the effect you want to realize as a snapshot.

Best Regards,
Yang
Community Support Team

[mcgeeks]

Hi Yang, thanks for responding. 

Our tests are such that sometimes you need test 1.  And at other times you need a separate test 2 or test 3.  But for some you need a combination of tests, in my example that was a combo of test 1, test 2, and test 3, and they want it counted as one test.

Let me give you an actual detailed example.  I'm posting a picture of the actual data as it comes from a huge database, it's hard to extract just that.  In this example the "sample" is a string of numbers on the left.  Then the next column is a distinct count of the sample and totals correctly.  Then the Count of Test Groups is a distinct count of test names (a text column) that gives the correct total of tests per sample (meaning per row) but does not add them up correctly for the total at the bottom. 

The TestCount column is one where I was trying to achieve that total with a combination of two measures.  Which is not achieving what I'm after.  It instead is just mimicking what I'm seeing in the Count of Test Groups column.  Here are those two measures:

CountTests = DISTINCTCOUNT('ASG Data'[Test])

TestCount = if( ISFILTERED('ASG Data'[Sample]), [CountTests], SUMX(values('ASG Data'[Sample]), [CountTests]))

I only need 2 columns with correct totals.  Count of samples which is already correct, and a test count with whichever column name.  I'm just having problems getting the total correct on that column with a distinct count of text test names per sample.

Here is a snapshot of the list without a sample expanded.

The count of sample is totaled correctly.  Either the Count of test groups column or the TestCount column TOTAL needs to read 90.

	1	1
	1	1
	1	1
	1	1
	1	2
	1	1
	1	1
	1	1
	1	1
	1	2
	1	2
	1	1
	1	3
	1	8
	1	7
	1	7
	1	7
	1	7
	1	7
	1	7
	1	7
	1	7
	1	8
Total	23	90

I know this was lengthy, but hopefully I've given enough information to explain the issue.  Thank you for your assistance on the matter.

Regards,

mcgeeks

[mcgeeks]

So I made progress. 

• I created a grouping for some tests. 
• I then put the grouping in the matrix visual. 
• It counts the ROWS correctly, and shows the test names on rows correctly, but shows the total at the bottom of the matrix for tests incorrectly. 
• I used a distinct count in the visual for that column to get the right count of tests per sample, but the total is garbage.  It's not summing a total of the column's test count, which is a count of text test names per row as one would expect.  It's a distinct count of the various names, and as such only ever goes up to the total number of tests ran on samples for that visual.  It will go up to 11 max per row (per sample).

If I can now get a sum of the count of test groups  in that matrix I'll be good.

[mcgeeks]

Follow up: I have the data in a matrix table.  A distinct count of sample works.  A non-distinct value for Test gives me way too many in the count.  We think due to the tests that should be grouped together in the count.  I was hoping that grouping the Tests could help but it doesn't seem to change the count at all.