Forum Discussion
Helper IIICount on Current Phase to Pie chart
Hello friend,
I have table:
| Project | Phase | Phase_Sort | Forecast |
| Project 1 | Construction | 2 | 1-Oct-21 |
| Project 2 | Design | 1 | 1-Jan-22 |
| Project 3 | Design | 1 | 1-Jan-22 |
| Project 4 | Design | 1 | 1-Jan-22 |
| Project 1 | Design | 1 | 1-Apr-18 |
I calculate current phase based on sort id for each project:
Measures:
Requirements: I would like to get count of Distinct Project based on Current phase and want to display on Pie chart. Whenever , I want to display on Pie chart, I am getting count of Design 4 and Construction 1:
But I would like to get: Design 3 and Construction 1 as we can see in table.
6 Replies
- bhanu_gautam
Super User
Jyaul1122 Calculate the Current Phase for each project:
DAX
CurrentPhase =
CALCULATE(
MAX(test[Phase]),
FILTER(
test,
test[Phase_Sort] = CALCULATE(MAX(test[Phase_Sort]), ALLEXCEPT(test, test[Project]))
)
)Then Count the distinct projects based on the Current Phase:
DAX
DistinctProjectCount =
CALCULATE(
DISTINCTCOUNT(test[Project]),
ALLEXCEPT(test, test[CurrentPhase])
)And at last Create a measure to use in the Pie Chart:
DAX
ProjectsByPhase =
CALCULATE(
DISTINCTCOUNT(test[Project]),
VALUES(test[CurrentPhase])
)Now, you can use the ProjectsByPhase measure in your Pie Chart to display the count of distinct projects based on the current phase.
Jyaul1122Thanks for your reply.
How can I get above without calculated column creation, means via only Measures.
Thanks
Jyaul
- V-yubandi-msft
Community Support
Hi Jyaul1122 ,
Use below one:
ProjectsByPhase = VAR ProjectsWithCurrentPhase = ADDCOLUMNS( -- Get all projects ignoring any Phase filter coming from the visual SUMMARIZE(ALL(test), test[Project]), "CurPhase", VAR CurProj = [Project] VAR MaxSort = CALCULATE( MAX(test[Phase_Sort]), ALL(test), -- Remove all filters so we consider all rows test[Project] = CurProj ) RETURN CALCULATE( MAX(test[Phase]), ALL(test), -- Again, remove all filters to compute the true max sort row test[Project] = CurProj, test[Phase_Sort] = MaxSort ) ) VAR SelectedPhase = SELECTEDVALUE(test[Phase]) RETURN COUNTROWS( FILTER(ProjectsWithCurrentPhase, [CurPhase] = SelectedPhase) )Result:
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
- V-yubandi-msft
Hi Jyaul1122 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you. - V-yubandi-msft
Hi Jyaul1122 ,
We noticed we haven't received a response from you yet, so we wanted to follow up and ensure the solution we provided addressed your issue. If you require any further assistance or have additional questions, please let us know.
Your feedback is valuable to us, and we look forward to hearing from you soon.
- V-yubandi-msft
Hi Jyaul1122 ,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.
Thank you.
