Forum Discussion
Microsoft EmployeeUse a calculated measure from a different Table/Matrix as a slicer
Hello,
I have the following Matrix where it'll show different LiveRates depending on the LiveCount and UnitCount of each of it rows:
As it can be seen the Live Rate is simply calculated by LiveCount/UnitCount
The LiveRate is calculated by the following query:
LiveRate = VAR LiveRateValue = IF(ISBLANK([LiveCount]), 0.00, DIVIDE([LiveCount], [UnitCount], 0))
RETURN
SWITCH(
TRUE(),
LiveRateValue = 1.0, "✔️ " & FORMAT(LiveRateValue, "0.00"),
LiveRateValue = 0.00, "🔴 " & FORMAT(LiveRateValue, "0.00"),
LiveRateValue > 0.00 && LiveRateValue < 1.0, "🟡 " & FORMAT(LiveRateValue, "0.00"),
FORMAT(LiveRateValue, "0.00")
)
What I wanted to do though, was create a slicer for my matrix where I could filter out all the rows depending on the Live Rate.
For example have a slicer where the number goes between
0.00 - 1
If I set it to 0.33 - 1 it would show me all the rows where the LiveRate is somewhere between there.
However, since this is a calculated measure I'm not sure if there's a way to use this as a Slicer. Is this possible? And how can it be achieved easily?
Thank you!
Hi SeungLee95
Sorry for the late response.
You cannot directly use a measure in a slicer since slicers only work with columns. To achieve filtering by LiveRate in a slicer, create a calculated column instead of using a measure. For example:
LiveRateColumn = DIVIDE([LiveCount], [UnitCount], 0)
This column can then be used in a numeric range slicer to filter rows between values like 0.33 to 1.0. You can still keep your original LiveRate measure for conditional formatting and display in the matrix. The column is only used for slicer-based filtering.
Best Regards,
Cheri Srikanth
8 Replies
Ashish_ExcelSolution Supplier
Hi,
Share the download link of the PBI file.
SeungLee95For security reasons and since the information could be confidential, I'm avoiding uploading the PBI file. Is there any chance you could suggest without?
- Jihwan_Kim
Super User
Hi,
Please consider using numeric range parameter feature in Power BI desktop.
https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-what-if?wt.mc_id=DP-MVP-5004989- SeungLee95
Hi Jihwan_Kim ,
Thank you for the response.
I've tried using the numeric range parameter but fail to actually apply it.
The problem I believe is I can't apply a parameter since the table values are not really measures, but columns with a count
Is there any other suggestions you would give me?
- v-csrikanth
Community Support
Hi SeungLee95
Sorry for the late response.
You cannot directly use a measure in a slicer since slicers only work with columns. To achieve filtering by LiveRate in a slicer, create a calculated column instead of using a measure. For example:
LiveRateColumn = DIVIDE([LiveCount], [UnitCount], 0)
This column can then be used in a numeric range slicer to filter rows between values like 0.33 to 1.0. You can still keep your original LiveRate measure for conditional formatting and display in the matrix. The column is only used for slicer-based filtering.
Best Regards,
Cheri Srikanth - v-csrikanth
Hi SeungLee95
We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth. - v-csrikanth
Hi SeungLee95
I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!
Best Regards,
Community Support Team _ C Srikanth. - v-csrikanth
Hi SeungLee95
It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!
Best Regards,
Community Support Team _ C Srikanth.
