Set analysis using dimension value

[dselgo_eidex]

Hello, so I have a table below which contains information on schools and their respective school districts:

Entity Code	Entity Type	District Code	Entity Name
1001	Building	1000	School A
1002	Building	1000	School B
1003	Building	1000	School C
1004	Building	1000	School D
1005	Building	1000	School E
1000	District	1000	District A

As you can see, each Building has an associated District Code. What I am trying to do is create a Straight Table like the following:

Building	District
School A	District A
School B	District A
School C	District A
School D	District A
School E	District A

For this straight table, I have Entity Name as the primary dimension, and I'm trying to figure out how to set up the set analysis to get the associated District Name. Right now I have

only({1<[Entity Type] = {'District'}, [District Code] = {$(=only({1} [District Code]))}>} [Entity Name])

but this is resulting in NULL values for District. I guess I'm just having trouble with understanding how the set analysis plays with the chart dimension and how to return the Entity Name on a different row.

[sunny_talwar · Accepted solution]

May be this

=Aggr(If([Entity Type] <> 'District', Only(TOTAL {<[District Code]>} {<[Entity Type] = {'District'}>} [Entity Name])), [Entity Name], [District Code])

[dselgo_eidex]

Sunny, could you explain why you have {<[District Code]>} {<[Entity Type] = {'District'}>}

I've never seen two set expressions like that before. Also, what does having [District Code] in its own set expression do?

[sunny_talwar]

My bad, I wanted to do this

=Aggr(If([Entity Type] <> 'District', Only(TOTAL <[District Code]> {<[Entity Type] = {'District'}>} [Entity Name])), [Entity Name], [District Code])

Incorrectly placed {} around <[District Code]>, but this is used for TOTAL qualifier and the other part is the set analysis

[dselgo_eidex]

Thanks for the help Sunny! That worked out perfectly.

[vinieme12]

I would suggest breaking the table in 2,

table 1 : WHERE ENTITY = SCHOOL

Entity Code	District Code	Entity Name
1001	1000	School A
1002	1000	School B
1003	1000	School C
1004	1000	School D
1005	1000	School E

Left JOIN

table 2 :  WHERE ENTITY = DISTRICT

District Code	Entity Name
1000	District A

[dselgo_eidex]

Normally I would agree, it would make things like this easier. But because of the way that I have my data model set up and the way that I need my app to perform, having both on the same table works better.

[vinieme12]

You just need to to join them as columns

LOAD

EntityCode,districtCode,EntityName

Where Entity = 'School';

Left join

LOAd  districtCode,EntityName as districtname

Where Entity = 'district';

using AGGR could possibly slow down app performance depending how many unique values the fields you are aggregating by

[dselgo_eidex]

I'm aware of the potential hit to app performance, but there are a relatively small number of unique values to AGGR over so I feel confident that it should be minimal. But I always do have the backup plan of joining [District Name] onto the table.