How do I create new tables based upon a condition?

[Worstkees]

Hi,

Clearly I’m not experienced enough yet so need some help:

How do I create new tables based upon a condition?

I have two tables:

One called FT with UniqueID, Consumerid, SequenceScore and an eventid

The other called Event with EventId and Eventname

For reporting issues I need 6 different tables for sequencescore =1,2,3,4,5 and 6

Where I can see the consumerid and eventid

FT

UniqueID	ConsumerID	SequenceScore	eventid
24567	246	5	4701
25678	327	2	5012
3214	328	4	5012
4567	327	1	5532

A ConsumerID cannot have the same SequenceScore twice.

Event

EventId	Eventname
4701	Event4701
4702	Event4702
4703	Eveent4704

What I’m looking for is 6 tables named SequenceScore1, SequenceScore2, SequenceScore3, SequenceScore4, SequenceScore5, SequenceScore6 with:

FT[UniqueID] -although I think this might not be needed	consumerid	eventid

Your help is appreciated!

(Worst)Kees

[Fowmy · Accepted solution]

@Worstkees 

I didn't understand the question very well, however, you can create a new table as follows

Table 1 = CALCULATETABLE ( ft, ft[SequenceScore] = 1 )

[v-cazheng-msft]

Hi @Worstkees ,

May I know whether your issue has been resolved? If you still have problems on it, could you please make what you want more clear? Such as how you want to arrange the fields in the visual? Thanks in advance!

How to Get Your Question Answered Quickly - Microsoft Power BI Community

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

[Worstkees · Accepted solution]

thanks @Fowmy 
I try to get to a situation where I can use (e.g.) the Decoposition Tree Visualization to show the order in which consumers went through the events.
For this I need to provide seperate fields for the first event, the second event etc.

Your solution gives me the FT table per sequence, and I can use lookupvalue to add the eventname to the table but as soon as I try to create a relationship with the original FT table (to filter on data in other tables) it tells me that a circular dependency was detected. Any extra suggestions?

Your help is truly appreciated! 

[Fowmy · Accepted solution]

@Worstkees 

I didn't understand the question very well, however, you can create a new table as follows

Table 1 = CALCULATETABLE ( ft, ft[SequenceScore] = 1 )