Hello friends,
I'm having difficulties creating a measure. I want to count the how manytimes a partner changed into different status.
The data looks like the following (Link pbix File) :
FACT_TABLE
Line
| PARTNER
| ID_ACTIVE
| ID_BANNED
| ID_DEACTIVATED
| VALID_FROM
| VALID_UNTIL |
1 | A | 2 | 1 | 1 | 01.01.2023 | 05.01.2023 |
| 2 | A | 2 | 2 | 1 | 01.01.2023 | 05.01.2023 |
| 3 | A | 2 | 1 | 1 | 05.01.2023 | 10.01.2023 |
| 4 | A | 1 | null | null | 10.01.2023 | 20.01.2023 |
| 5 | A | 3 | null | 2 | 20.01.2023 | 23.01.2023 |
| 6 | A | 3 | null | 3 | 20.01.2023 | 23.01.2023 |
| 7 | A | 3 | null | 3 | 23.01.2023 | 31.01.2023 |
| 8 | A | 1 | null | null | 31.01.2023 | 10.02.2023 |
| 9 | A | 3 | null
| 1
| 10.02.2023 | 01.01.2200 |
DIM_ACTIVE
ID_ACTIVE
| STATUS |
| 1 | active |
| 2 | banned |
| 3 | deactivated |
DIM_BANNED und DIM_DEACTIVATED contain different reasons, why a partner is banned and/or deactivated. A Partner could be banned or deactived for more than one reason at once (see line 1 & 2). Therefore multiple entries in the FACT_TABLE can be valid at once (Line 1 & 2, // 5 & 6).
I want to know how many different reasons (BANNED and DEACTIVATED) the Partner went through. Common DAX-Function such as COUNT(ID_DEACTIVATED) = 7 or DISTINCTCOUNT(ID_DEACTIVATED) = 3 are wrong, if you look at the data from a historical point of view:
Date
| BANNED
| DEACTIVATED |
| 01.01.2023 | added reason 1 & 2 | added reason 1 |
| 05.01.2023 | deleted reason 2 | |
| 10.01.2023 | deleted reason 1 | deleted reason 1 |
| 20.01.2023 | | added reason 2 & 3 |
| 23.01.2023 | | deleted reason 2 |
| 31.01.2023 | | deleted reason 3 |
| 10.02.2023 | | added reason 1 |
The expected result is 4, as there a 4 times in history, where a new deactivation reason was added.
The expected result for banned reasons is 2. In total the Partner changed into 6 different reasons.
How do I create a measure like this?
Thanks in advance 🙂
