PowerBI Primary Constituency Code

Mark Palfrey

Hi all

Is there a way to filter on Primary Constituency Code in PowerBI? I have the Constituent and Constituency Code tables open but cant see what the appropriate option would be for this. I am assuming I am missing something or its just not possible yet?

Many thanks

Mark

Matt Thacker

Hey @Mark Palfrey, how are you denoting that a constituent code is the primary in RE? Or does your organization just consider certain constituent codes “higher” than others? For example, if someone was a board member and a donor, the board constituent code would be the primary?

Mark Palfrey

Thank you Matt for the reply. Sorry I should have made it clear in the first place. We are using the out of the box Primary Constituency Code functionality. In RE query there is a node for Primary Constituency Code where RE works it out for you based on built in rules. I was hoping there would be something similar like this to pick from via the tables available in PBI.

Matt Thacker

@Mark Palfrey

Ah, I'm tracking now. So I've played around it and it looks like when you run a query with the Primary Constituent information, it returns the code with the small integer lookup value. In Power BI, on the Constituent Codes table, the second column is the constituent_code_id. Whatever the lower value of that column is is what's returned as the Primary Constituent. So with that, this is how I worked out a solution:

On the constituent codes table, I created this formula: (I've also added the text if the image is too hard to read)

= Table.Buffer(Table.Sort(#"Constituent codes1",{{"constituent_id", Order.Ascending}, {"constituent_code_id", Order.Ascending}}))

After I sorted, I highlighted the constituent_id column and removed duplicates. This left me with only the lowest valued constituent code id.

You could leave it here and just create a relationship between the two tables, but if you only need the constituent code description, I'd merge it to the constituent table. It'll make your model faster and any DAX you'd have to write easier. So you'd end up with something like this:

Mark Palfrey

Amazing thank you so much for this! Worked a treat! I have another questions which I will upload in a seperate post.