Displaying values in a nested gallery based on values from other galleries.

[Termigez1]

Hello, 

I have 3 data sources. Table A which is my main table with all the records and all the columns, it looks like this:

Name	Date	Value
Name 1	01.01	1
Name 1	02.01	2
Name 1	03.01	3
Name 2	01.01	1

the other 2 tables are Table B which is a list of unique Names from Table A and Table C which is a list of unique Dates from Table A (I am doing it because od delegation issue). 

I need to display my data in such way:

	01.01	02.01	03.01
Name 1	1	2	3
Name 2	1	2	3
Name 3	1	1	1

For my needs it must be a vertical gallery (with my unique names from table B as a data source) with a second horizontal gallery nested inside it (I have no idea whether I should use Table A or Table C as a data source)

I can imagine that I will create a third horizontal gallery (again with table C as a data source) above both of them with my unique Dates but I really can't find a way to display my Values in the second horizontal gallery that will match corresponding Name from the first gallery and will match the order of Dates from the third gallery. I would really appreciate your help. 

[eka24]

Try watching the video

https://youtu.be/EnBpWBb51hs?list=TLPQMjkwNzIwMjBWQhBpO5ffug&t=3

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[v-bofeng-msft · Accepted solution]

Hi @Termigez1 :

Firstly,A gallery control can only achieve one level of nesting, that is to say, in a sub-gallery, you cannot nest another gallery.

Secondly,If you just want to display the data in the following format, I suggest you use the following method.

	01.01	02.01	03.01
Name 1	1	2	3
Name 2	1	2	3
Name 3	1	1	1

My data source:

Solution1:

1\Add a Gallery

Items:

TableB

2\Add a lable into this gallery

Text

ThisItem.Name

3\Add a sub-gallery into this gallery

Items

Filter(TableA,ThisItem.Name=Name)

Or(If you need to sum up Value)

AddColumns(GroupBy(Filter(TableA,ThisItem.Name=Name),"Date","TheGroup"),"Value",Sum(TheGroup,Value))

4\Add a label into this sub gallery

Text

ThisItem.Date

5\Add a label into this sub gallery

Text

ThisItem.Value

Solution2:

1\Add a datatable control and set it's items property to:

AddColumns(
    TableB,
    "01.01",
    LookUp(
        TableA,
        Name = TableB[@Name] && Date = "01.01"
    ).Value,
    "02.01",
    LookUp(
        TableA,
        Name = TableB[@Name] && Date = "02.01"
    ).Value,
    "03.01",
    LookUp(
        TableA,
        Name = TableB[@Name] && Date = "03.01"
    ).Value
)

Or(If you need to sum up Value)

AddColumns(
    TableB,
    "01.01",
    Sum(
        Filter(
            TableA,
            Name = TableB[@Name] && Date = "01.01"
        ),
        Value
    ),
    "02.01",
    Sum(
        Filter(
            TableA,
            Name = TableB[@Name] && Date = "02.01"
        ),
        Value
    ),
    "03.01",
    Sum(
        Filter(
            TableA,
            Name = TableB[@Name] && Date = "03.01"
        ),
        Value
    )
)

Best Regards,

Bof