Unable Calculate categorized (aggregate/ count) movement of Scores between on Start Date/ End date

[Anonymous]

UPDATE
I managed to fully bucket all my categories using a Switch(True()) measure, but I cant do any math Count/ Distinct Count off of my numbers derived from here, i can only view it side by side to my Relationship_ID column, please help

I would wan

Hi All,

I am new to the powerBi world and not from a programming background. I need your help with something rudimentary.I have set up a measure comparing/Conditions a score between a Start Date measure (sums score by date) and an End Date Measure (sums score by date), to Bucket the movement in score between MIN of date and Max of Date. I want to look at this score as a relationship between a cusomer and the internal associate (both columns in the same table and an ID column created for the relationship as well) ,.

Capturing the score by the start date column on the data set (Not necessarily mapped to the row context of the relationship)

Following are the issues that i am facing:

1. I am having trouble getting the count (Distinct count) of the these bucket sizes for each quarter, Following is the bucketting measure (IF) and due to not being able to count, following that is a Countrows measure used to capture the bucket size for each measure category

Categorization:

Capturing the above categories (seperately by category):

2. I have created a measure to also capture Active relationships ( which did not end in the start date and not a new relatiosship based on dynamic date ranges on the report), However I cannot count/ apply filters with these, Hence I am just filtering out any relationship(occurance} where 'start date - end date = 0' 

The thing is, Im not certain how my data is connected to the relationship, However when i put the relationship ID and my bucketing variable along with my time-based score capting.

Following is my main table data, unfortunately i am not able to share a cut of the data,  In the data for each customer/ Associate relationship, a new row is repeated each quarter with the score

RelationshipID	Unique ID
FY	Financial year
Quarter
Customer Name
Associate
Relationship Score
Quarter Start Date	Connected to my calender table

When I look at the relationship at a on a table visualization, the Movement seem to be correct for each relationship. however I cannot, However I also cannot filter out the Active relationships (which has a score for start Date as well as end date [ start date <> End Date]

Also, I cant seem to figure out a way to distringuish between relationships that 'Exited' and 'New' relationships for each quarter (Or dynamic time selection) (Ex: Q1 - Q3) for me to locate .

Apperciate your help, Looking forward to hearing from someone who'd get the context too. I feel like my approach needs a change or am i nearly there?

[Anonymous]

UPDATE 

I was able to bucket my occurances better using the SWITCH( TRUE() Function, However I am now facing an issues where occurances that are moving from a '1' to a '2' are getting captured as a "constant", I was hoping the spill from this measure would be all "ended" but some changes are getting captured there as welll 😕

Also what would be the best way to get a count of this measure results?

[v-yuta-msft]

@Anonymous ,

UPDATE 

I was able to bucket my occurances better using the SWITCH( TRUE() Function, However I am now facing an issues where occurances that are moving from a '1' to a '2' are getting captured as a "constant", I was hoping the spill from this measure would be all "ended" but some changes are getting captured there as welll 😕

---

I'm not very clear about this requirement, could you share some sample data and give the expected result?

Regards,

Jimmy Tao

[Anonymous · Accepted solution]

Thanks for you reply. I actually solved all my initial bucketing/ categorization issues.
However I still cant seem to get a correct value for the distinct count by category based on the "Relationship ID" column which is a unique ID between the customer, Associate and the client. (unfortunately this is sensitive data - sorry about that)
x`
following is my categorization measure (which works perfectly)

I used the following formula to capture the valuefor each bucket from the above formula, which seem to get a count of all the occurances of RelatiosshipID under the category. for example the count will be 2 counting the occurances at start date as well as the end date. (the screenshot is for 1 of the categories)