Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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)
@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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |