Counting number of rows where there's one than more ocurrence

[qmestu]

Hi,

I have the following data:

id	case	study	year
1	A	S1	2023
2	A	S2	2023
3	B	S1	2023
4	C	S1	2022
5	C	S2	2023

I'm trying to count the number of cases for each study where the case had another study in the same year. With my sample data, this would only be true for case A.

Thanks.

[selimovd · Accepted solution]

Hey @qmestu ,

please check the attached file.

The following measure should work:

# Cases for additional studies same year = 
VAR _VirtualTable = 
ADDCOLUMNS (
    SUMMARIZE (
        myTable,
        myTable[study],
        myTable[case],
        myTable[year]
    ),
    "@CasesInSameYear",
    VAR _studyCurrentRow = myTable[study]
    VAR _caseCurrentRow = myTable[case]
    VAR _yearCurrentRow = myTable[year]
    RETURN
        CALCULATE (
            COUNTROWS( myTable ),
            myTable[case] = _caseCurrentRow
            && myTable[year] = _yearCurrentRow,
            ALL(myTable)
        ) - 1
)
RETURN
    SUMX( _VirtualTable, [@CasesInSameYear] )

In this case it will show a 1 or larger number if there is more than one match:

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic