Create static table from existing table

[Anonymous]

Hi all,

I cant figure out a possible solutions... hope someone here can help me into the right direction 🙂

Basically I have the following table:

Article	Competitor	Week	Value
1	Red	1	100
1	Red	2	300
1	Red	3	500
1	Red	4	700
2	Green	4	200
2	Blue	2	400
2	Blue	3	100
2	Blue	4	500

I want to create a new table where I only retrieve 1 row per article + competitor for the latest (max) week.

So it would look like this:

Article	Competitor	Week	Value
1	Red	4	700
2	Green	4	200
2	Blue	4	500

I tried selectedcolumns, but then I would get an error saying: "A table of multiple values was supplied where a single value was expected"
Im now kinda stuck. Does anyone have an idea?

Thanks in advance! 🙂

[tamerj1 · Accepted solution]

Hi @Anonymous 
It depends. But based on the example that you have provided this should work. 

New Table =
TOPN ( 1, 'Table', 'Table'[Week] )

However, the real data might be have different scenarios/requirements. For example if the last week for red is 3, what result shall be expected? Is it the last record for each Competitor independently or only the records of the very last week? The above solution conforms with the 2nd assumption. If the first assumption is true then you may try

New Table =
GENERATE (
    SUMMARIZE ( 'Table', 'Table'[Article], 'Table'[Competitor] ),
    VAR LastRecord =
        TOPN ( 1, 'Table', 'Table'[Week] )
    VAR Week =
        MAXX ( LastRecord, 'Table'[Week] )
    VAR Value =
        MAXX ( LastRecord, 'Table'[Value] )
    RETURN
        ROW ( "Week", Week, "Vale", Value )
)

[Anonymous]

Hi Tamerj1,

Thanks for your reply!

New Table =
TOPN ( 1, 'Table', 'Table'[Week] )

This seemed to be very helpful! I think I can work my way out with this. 🙂

[AnthonyGenovese]

What have you tried so far? Are you looking for this to be a calculated table?

If this post was helpful, please kudos or accept the answer as a solution.
~ Anthony Genovese
Need more PBI help? PM me for affordable, dedicated training or consultant recomendations!