Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

count attributes in a history table with multiple parallel entries

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

21101.01.202305.01.2023
2A22101.01.202305.01.2023
3A21105.01.202310.01.2023
4A1nullnull10.01.202320.01.2023
5A3null220.01.202323.01.2023
6A3null320.01.202323.01.2023
7A3null323.01.202331.01.2023
8A1nullnull31.01.202310.02.2023
9A3null
1
10.02.202301.01.2200

 

DIM_ACTIVE

ID_ACTIVE   
STATUS
1active
2banned
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.2023added reason 1 & 2added reason 1
05.01.2023deleted reason 2 
10.01.2023deleted reason 1deleted 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 🙂

 

2 Replies

  • please explain lines 5 and 6.  The intervals are identical?

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hey,

      that's correct. A Partner can be deactivated for multiple reasons at the same time. In this case the Partner has Reason 2 AND 3.