topic Re: Create static table from existing table in DAX Commands and Tips

Create static table from existing table

Anonymous — Tue, 08 Aug 2023 14:59:53 GMT

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! 🙂

Re: Create static table from existing table

AnthonyGenovese — Tue, 08 Aug 2023 18:10:44 GMT

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!

Re: Create static table from existing table

tamerj1 — Tue, 08 Aug 2023 18:55:45 GMT

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 ) )

Re: Create static table from existing table

Anonymous — Wed, 09 Aug 2023 11:50:36 GMT

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. 🙂