[npnigroParticipant Started: 1Replies: 7Total: 8]

Thanks so much for thinking it through, Tom! I really appreciate your insights and your time. In the end, adding ~50 measures was a pain once, but subsequent additions take < 1 min so it shouldn’t be too much of a problem. My objectives were to keep maintenance as simple as possible and to reduce unnecessary data. I think my approach will work for my needs.

Nick

[npnigroParticipant Started: 1Replies: 7Total: 8]

Thanks! Regarding your question, no, IDs are only in one table: City, County, Utility, or State depending on their scope. Some policies are only for a particular city, others are for a county, etc. My idea is to use a pseudo-hierarchy allowing a user to look at a particular city and see all the policies that affect that city (including relevant county, utility, and state policies).

 

 

[npnigroParticipant Started: 1Replies: 7Total: 8]

Here’s a working example of what I’m trying to do.

http://1drv.ms/1MBPhRu

 

[npnigroParticipant Started: 1Replies: 7Total: 8]

Thanks for your help! I ended up using a combination of the tricks (the DAX table from Kasper and power queries to dynamically join tables).

One thing I struggled with was plotting data from one table column as a separate series and comparing that to a different field. See image attached. As a workaround, I made a measure for each unique value. Normally, you’d use the Legend (series) area of a pivot chart for that, but adding another field duplicates that data for each value in the series. Is there any easy workaround for that? I suspect you could use a relationship, but I’m not sure how to do it.

Attachments:

You must be logged in to view attached files.

[npnigroParticipant Started: 1Replies: 7Total: 8]

That would be really great. Do you know what the query would look like to do that?

[npnigroParticipant Started: 1Replies: 7Total: 8]

Thanks for pulling this together! I really appreciate your help. This is another interesting approach. I like that the table used by PowerPivot is dynamic.

[npnigroParticipant Started: 1Replies: 7Total: 8]

Thanks! This is an interesting approach. I’m trying to avoid adding a column for each “Item” because there will be countless items. Do you know of a way to accomplish this task with a measure?

[Author]

Posts