How to calculate how many times a combination of certain values belong to an ID

[ChrisLoan]

Hi Everyone!

I have a question for my fellow BI people. I'm gonna use dummy data to give an idea of our DWH situation at our company.

So we have a Fact table Matchphases with surrogate keys linked to different dimension tables.

 

  

I need to write a measure that calculates how many times Candidates have a combination of Matchphases 1, 2 AND 4.

So in my example the answer would be 1, cause Candidate 1 is the only one who has matchphases 1, 2 and 4 in his history of matchphases.

Fact table is connected to the dimension tables using the many to one connection on the SK's:

Hope someone can help me out! Thanks in advance 😊

[OwenAuger · Accepted solution]

Hi @ChrisLoan 

The idea is similar to the one discussed in this article on applying AND logic to multiple selections.

The difference is that you have a fixed list of MatchPhases.

Based on your dummy data, you can adapt the measure from the above article to something like this:

Candidates with  MatchPhases 1,2,4 =
VAR MatchPhases = { "Matchphase 1", "Matchphase 2", "Matchphase 4" }
VAR MatchPhasesLineage =
    TREATAS ( MatchPhases, 'Dim MatchPhases'[MatchPhase_SK] )
VAR NumMatchPhases =
    COUNTROWS ( MatchPhases )
VAR CandidatesMatchPhases =
    CALCULATETABLE (
        SUMMARIZE (
            'Fact MatchPhases',
            'Dim Candidates'[Candidate_SK],
            'Dim MatchPhases'[MatchPhase_SK]
        ),
        MatchPhasesLineage
    )
VAR CandidatesWithNumMatchPhases =
    GROUPBY (
        CandidatesMatchPhases,
        'Dim Candidates'[Candidate_SK],
        "@MatchPhases", SUMX ( CURRENTGROUP (), 1 )
    )
VAR CandidatesWithAllMatchPhases =
    FILTER ( CandidatesWithNumMatchPhases, [@MatchPhases] = NumMatchPhases )
VAR Result =
    COUNTROWS ( CandidatesWithAllMatchPhases )
RETURN
    Result

The MatchPhases variable can be updated to any list of values.

Does something like that work?

Regards,

Owen

[ChrisLoan]

Thanks so much for the quick response Owen! You're the man 😊

So far the results look promising, and it works!

Quick follow up question though, something I forgot to mention in my original post;

is there a way to change the measure to check for candidates who have matchphase 1 AND Matchphase 2 OR 3? That would be great!

[OwenAuger]

@ChrisLoan  you're welcome 🙂

For Matchphase 1 AND Matchphase 2 OR 3, the measure is actually simpler to write.

This is because we no longer need to check if every Matchphase is present from a particular set, but instead check whether any Matchpase from the first set is present, and any Matchphase from the second set is present.

To do this, we can find the Candidates corresponding to two different Matchphase sets, and take the intersection.

One way to write it is:

Candidates with  MatchPhases 1 and (2 or 3) = 
VAR MatchPhases_1 = { "Matchphase 1" }
VAR MatchPhases_2 = { "Matchphase 2", "Matchphase 3" }
VAR Candidates_1 =
    CALCULATETABLE (
        SUMMARIZE ( 'Fact MatchPhases','Dim Candidates'[Candidate SK] ),
        TREATAS ( MatchPhases_1, 'Dim MatchPhases'[Matchphase SK] )
    )
VAR Candidates_2 =
    CALCULATETABLE (
        SUMMARIZE ( 'Fact MatchPhases','Dim Candidates'[Candidate SK] ),
        TREATAS ( MatchPhases_2, 'Dim MatchPhases'[Matchphase SK] )
    )
RETURN
    COUNTROWS ( INTERSECT ( Candidates_1, Candidates_2 ) )

In this particular example, we could also make use of Candidate SK in the fact table, and replace

SUMMARIZE ( 'Fact MatchPhases','Dim Candidates'[Candidate SK] )

with

VALUES ( 'Fact MatchPhases'[Candidate SK] )

Regards,

Owen

[ChrisLoan]

Thanks so much for your quick response and help Owen, I'm gonna try this ASAP en I'll let you know if it works! 😉