1

I am doing membership statistics for an organization. In our membership_subscriber table, we have the the dates when the membership begins and ends. There are multi year memberships. I need to show the statistics for a specific year.

I know that I can have a maximum of 6 years for a membership. So I created a new query using the table where I put the 6 years of membership each in a column. If a membership is only valid 1 year, only the first column will be filled, the rest will be null. After that I "unpivoted" those columns and I got my result. After removing the null values, I have the table I need. In this view, I can now see the membership_Subscriber_ID and the years that ID had membership. I can now link this to the membership_subscriber table and I can create a slicer based on my view.

This way I can easily filter for the 2020 membership for example, and the number of members for each type of membership. I count the number of ID for each type of membership, and I get the results I want.

If I should do this another way, please tell me about it. I am a beginner and only had the idea to do this while following courses on powerBI.

On the other hand, if I create a measure

count_members = calculate(COUNT(si_membershipsubscribers[si_membershipsubscriberid]))

As soon as I add this measure to my query the link breaks. I now have membership valid from 2016 to 2016 appear in my query for 2020 when I add the measure. I count the same field in the measure that I counted before.

Here are two images showing before/after I add the measure to my query: Before measure After measure

In these images you can see I use a slicer to select only 2022 membership. We have only 3 types of membership visible in 2022. But as soon as I use the measure instead of the count, the link between my view and the original table breaks. I have no idea why, and it basically means I cannot use measures in PowerBI.

So if you have any idea why I have this behavior or any suggestion for a change in the way I do things, it's welcome. Or if you can point me to a resource that can explain how to do this, that would be good too.

Ben Cor
  • 11

2 Answers2

0

I would try setting the relationship's Cross-filter direction = Both. This seems necessary as you are asking for the count of memberships per year.

The CALCULATE function appears redundant. I would consider DISTINCTCOUNT in place of COUNT, but it depends on your requirements.

Mike Honey
  • 2,632
0

I have the same problem. My method is Foobar = "abc" and if I add that to the table data, it breaks connections and I get all results or probably even something worse. I have used Cross-filter direction = Both, so that did not solve the case. Did you ever find out the solution?

Timo Riikonen
  • 111