Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Create static table from existing table

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:

ArticleCompetitorWeekValue
1Red1100
1Red2300
1Red3500
1Red4700
2Green4200
2Blue2400
2Blue3100
2Blue4500

 

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:

ArticleCompetitorWeekValue
1Red4700
2Green4200
2Blue4500

 

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

static
Table
  • tamerj1's avatar
    tamerj1
    3 years ago

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

3 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
    AnthonyGenovese
    Resolver III
    3 years ago

    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!