Forum Discussion
help needed for calculating a measure
hi.
i have a requirement for below table. I have below table and in Power BI report i have slicer for Agent column.
My requirement is when i select multiple values from slicer (Agent)
| Name | Agent(Slicer) | Statecode | State |
| abc | IE | 3 | Red |
| abc | Mozilla | 3 | Red |
| abc | chrome | 1 | Green |
| abc | Ios | 2 | white |
| abc | edge | 1 | green |
| xyz | IE | 3 | Red |
| xyz | Mozilla | 3 | Red |
| xyz | chrome | 3 | Red |
| xyz | Ios | 2 | green |
| xyz | edge | 2 | green |
| bbc | IE | 3 | Red |
| bbc | Mozilla | 2 | white |
| bbc | chrome | 2 | white |
| bbc | Ios | 2 | white |
| bbc | edge | 3 | Red |
I need to show for each 'Name' value, the Min(Statecode) value
For example1: if Slicer selected values are (IE,Chrome,Mozilla)
Name abc: Corresponding Min(stateInt) for slicer selection is 1 Green
Name ade: corresponding Min(stateInt) for slicer selection is 3 Red
Name bbc:Corresponding Min sateInt for slicer selection is 2 white
Example2:Slicer selected (IE,Mozilla)
Name abc : Corresponding min(StateInt) for the slicer selection is 3 Red
Name ade: corresponding Min(stateInt) for slicer selection is 3 Red
Name bbc: Corresponding Min sateInt for slicer selection is 2 white
i also need to show in three diffrent KPI cards one for each StateCode 1, 2, and 3 displaying count(Name) in three cards
In this example2 Count(Name) for Red = 2
Count(Name) for Green = 0
Count(Name) for White = 1
Help is much appreciated, Thanks
Hi, srk_powerbi
The above picture is before I fix.
I posted again yesterday, but it was without a picture.
Sorry for that.
You can check my yesterday's reply to this post. Or,
please check the below picture and link.
https://www.dropbox.com/s/8xpx0dhgq2qvv01/srk_powerbi%202021%200325.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster.
5 Replies
- Jihwan_Kim
Super User
Hi, srk_powerbi
Please kindly check the below picture if it is what you needed.
if not, please let me know.
Also, sample pbix file link is down below.
inside the pbix file, you can find all measures.
I hope it helps.
https://www.dropbox.com/s/8xpx0dhgq2qvv01/srk_powerbi%202021%200325.pbix?dl=0
Did I answer your question? Mark my post as a solution!
Appreciate your Kudos!!
- srk_powerbi
Helper II
hi Jim,
it is correct, but i want the 'custom' value to be shown in th e slicer as well...,in the above pic cutsom value is not shown in slicer
- Jihwan_Kim
Super User
Hi, srk_powerbi
The above picture is before I fix.
I posted again yesterday, but it was without a picture.
Sorry for that.
You can check my yesterday's reply to this post. Or,
please check the below picture and link.
https://www.dropbox.com/s/8xpx0dhgq2qvv01/srk_powerbi%202021%200325.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster.
- srk_powerbi
Helper II
HI Jihwan_Kim it looks perfect, thanks for solution. But i have minor change to the requirement, so the table has additional records where Statecode has another value '0'. I still need the same result as before ignoring this 0 value, and return 1 as min,. Can you please help. see the table below.
Name Agent(Slicer) Statecode State abc IE 3 Red abc Mozilla 3 Red abc chrome 1 Green abc Ios 2 white abc custom 0 none abc edge 1 green xyz IE 3 Red xyz Mozilla 3 Red xyz chrome 3 Red xyz Ios 2 green xyz edge 2 green xyz custom 0 none bbc IE 3 Red bbc Mozilla 2 white bbc chrome 2 white bbc Ios 2 white bbc edge 3 Red bbc custom 0 none - Jihwan_Kim
Super User
Hi, srk_powerbi
Thank you for your feedback.
Please correct me if I misunderstood your question.
- "custom" is selected together with other agents, ignore 0 value.
- only "custom" is selected, then the result is "none".
Based on my assumption above, I amended measures.
please check the link down below.
https://www.dropbox.com/s/8xpx0dhgq2qvv01/srk_powerbi%202021%200325.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster.