AND filter based on ID and date

[grggmrtn]

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		B

I 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?

[Fowmy]

@grggmrtn 

You create a MATRIX in the following layout. I used a column for Value but your measure will go in there:

________________________

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 🙂

YouTube
LinkedIn

[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 · Accepted solution]

@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 🙂

YouTube
LinkedIn

[grggmrtn]

Thank you @Fowmy - that worked great 🙂

[harshnathani]

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)

[amitchandak]

@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