How to model a process

[MiKeZZa]

Hi community,

I have a process which is stored in this way right now:

Client	StartDate Step 1	StopDate Step 1	Indicator Step 1 reached	StartDate Step 2	StopDate Step 2	Indicator Step 2 reached	StartDate Step 3	StopDate Step 3	Indicator Step 3 reached	StartDate Step 4	StopDate Step 4	Indicator Step 4 reached
100	2019-01-06	2019-01-08	1	2019-01-12	2019-01-15	1	NULL	NULL	NULL	2019-02-01	2019-04-02	1
120	2019-01-01	2019-01-06	1	2019-01-09	2019-01-12	1	2019-02-01	2019-04-02	1	NULL	NULL	NULL

As you can see some steps can be empty. My basic question is: how to model this? In the above way or going this way:

Client	Stepname	Startdate	Stopdate	Indicator reached
Client	Step 1	2019-01-01	....	1
......	......	......	......	......

And so on....

Or even this way came to my mind:

Client	Date	Step
100	2019-01-01	1
100	2019-01-02	1
100	....	....

To make it a little more complex; there is a second fact-table; this table has information about campaigns that have can be active and can have a complete different period:

ClientID	Start	Stop	Product category
100	2018-12-16	2019-02-12	89
100	2019-02-13	2019-03-12	88
120	2019-02-01	2019-02-22	73

And those facts must be presented in a combination also...

I will be able to prepare the data in every shape I'll need (can do it in SQL by example).... Can some people give me an advice about how to handle this. I've been working as BI-developer for many years but this is a hard case....

[v-yuta-msft]

@MiKeZZa ,

Have you solved your issue by now? If you have, could you please help mark the correct answer to finish the thread? Your contribution will be much appreciated.

Regards,

Jimmy Tao

[v-yuta-msft]

@MiKeZZa ,

The data you provided seems not to be the raw data. To be general, you may use "Unpivot" in query editor. However, if you still can't solve it, please share some raw data and expected result?

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.