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.
Hi, I am struggling to get my desired table output with the addition of using measure.
For context, I am counting the # of tasks that are created by quarter & year. However, I want to be able to filter the tasks by their 'Label' field, getting the # of tasks containing specific keywords in the 'Label field'. Before doing any filtering by label, I get output like this, which is what I want:
As you can see from the slicer, the tasks will have a series of words in the 'Label' field defining what type of task they are. However, I wanted to make this easier to use, so I decided to create a 'contains' slicer that allows users to filter tasks based on if they contain any of the singular label keywords. I created a separate table called 'PTeams' contaning each of the label keywords and created this measure:
I then create a slicer using the PTeams table, and the measure correctly filters the table with tasks that only contain the keywords selected in the slicer as seen here:
However, the table isn't grouping the quarters and is treating each task as a separate row, whereas I want a count of the # of tasks created each quarter. When I remove the Labels field as a column, nothing will show up.
How can I get the output I was getting in the first screenshot using the 'contains' slicer and without displaying any additional fields? The output I want is this, but using the 'contains' slicer I created instead:
Solved! Go to Solution.
@Anonymous try this measure:
Counts =
CALCULATE (
COUNTROWS ( PTeams ),
FILTER (
PTeams,
SUMX (
SearchTable,
IF ( CONTAINSSTRING ( PTeams[Label], SearchTable[SearchColumn] ), 1 )
) >= 1
)
)
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Anonymous try this measure:
Counts =
CALCULATE (
COUNTROWS ( PTeams ),
FILTER (
PTeams,
SUMX (
SearchTable,
IF ( CONTAINSSTRING ( PTeams[Label], SearchTable[SearchColumn] ), 1 )
) >= 1
)
)
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Anonymous try this measure:
New Column =
VAR __slicerValue = SELECTEDVALUE ( SlicerTable[SlicerCOlumn]
RETURN
CALCULATE (
COUNTROWS ( PTeams ),
CONTAINSSTRING ( PTeams[Label], __slicerValue )
)
and use above measure in the visual.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi, thank you! I believe the solution you provided is working correctly. My previous measure allowed me to select multiple fields in the slicer and correctly filtered out the tasks.
The solution you provided only correctly works if I select one of the options. If I select more than one, the filter breaks. How could I change the measure to support multi select?
Thank you
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 |
---|---|
115 | |
101 | |
68 | |
68 | |
43 |
User | Count |
---|---|
145 | |
106 | |
105 | |
90 | |
65 |