Forum Discussion
Post PatronAND filter based on ID and date
I need to isolate data for PersonID that have two specific values (in two different rows, of course), per week. PersonID that only have one of the values need to be ignored.
I've created a matrix table to show the data, but that's not a requirement, if anyone knows a better way.
I've tried some different solutions, most from this forum, but none of them seem to be working the way I need them to.
Sample data:
PersonID WeekNr Service
1 12 A
1 12 B
1 13 A
1 13 B
1 14 A
1 15 A
2 12 A
2 13 B
2 15 A
2 15 B
2 16 A
2 16 B
3 12 B
3 13 B
3 13 A
3 14 B
3 15 A
3 15 BI need to make a matrix visualisation, that will show PersonID for everyone that has Service A AND B per week, with a value that comes from a different table (price per service), like this:
Right now I have the matrix working, but it's showing me everything, as well as for PersonID that only have one of the services etc.
Is there any way I can get what I need?
grggmrtn
Maybe this?Measure = VAR A = CALCULATE( COUNTROWS(VALUES(Data[Service])), ALLSELECTED(Data[Service]) ) RETURN IF( A > 1, SUM(Data[Value]), BLANK() )________________________
If my answer was helpful, please mark this post as a solution, this will also help others!.
Click on the Thumbs-Up icon if you like this reply 🙂
7 Replies
- amitchandak
Super User
grggmrtn , try a measure like
countx(filter(summarize(Table,table[personID], "_1", calculate(distinctcount(Table[Service]),Table[Service] in {"A","B"})),[_1] =2),[personID])
display only personId and Week
- grggmrtn
And.. what am I supposed to do with this measure?
Displaying only PersonID and week isn't going to work either, because the value (price per week) is per service - so I need to display the service as well
Hi grggmrtn ,
Not very clear.
You can try SUMMARIZE (Table,Table[id], Table[Sevice])
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)- Fowmy
- grggmrtn
Your matrix looks like the one I already have, and you've run into the same problems that I ahve.
Person 1 - you have values listed for Service A for weeks 14 and 15. Since there is no Service B for those weeks, your value shouldn't be displayed.
And what would happen if you have a PersonID 4 that only has Service A? They would also show up in that matrix - but that's what I'm trying to avoid.
I need to show PersonID that have BOTH, per weeknr
- Fowmy
grggmrtn
Maybe this?Measure = VAR A = CALCULATE( COUNTROWS(VALUES(Data[Service])), ALLSELECTED(Data[Service]) ) RETURN IF( A > 1, SUM(Data[Value]), BLANK() )________________________
If my answer was helpful, please mark this post as a solution, this will also help others!.
Click on the Thumbs-Up icon if you like this reply 🙂
