Forum Discussion
Measure for filtering a table based on filtered values from another table
Hi,
In the below screenshot,
"Delivery Lead" is the lead filter, which is from a master table called Engagement tracker.
"Project Name" column in the table visual is from a table called project Dimension which contains all the project names.
Then the count values, observation & NC that is calculated from IDR table
Now the relationship part:
1) Engagement tracker(Project Name) ---- project Dimension(Project Name)
2) IDR(Project Name) --- Engagement tracker(Project Name)
based on the selected "Delivery Lead" in need to calculate the observation & NC count from IDR table..
show the total NC count for that "Delivery Lead" if no projects are selected from that table view , else show the NC count for that specific project selected in the table view
basically need a measure which calculates the count of Project Name by filtering IDR table based on below conditions:
1)Project Name = values filtered from project Dimension where lead = selected value of Delivery Lead
2)Category = NC and Finding Status = Open.
for eg: if Lead = A,
filtered project names = ENG 1, ENG 2
Total NC count = 2
Data views:
Engagement tracker:
| Project Name | Lead |
| ENG 1 | A |
| ENG 2 | A |
| ENG 3 | B |
| ENG 4 | C |
project Dimension:
| Project Name |
| ENG 1 |
| ENG 2 |
| ENG 3 |
| ENG 4 |
| ENG 5 |
IDR:
| Project Name | Category | Finding Status |
| ENG 1 | NC | Closed |
| ENG 1 | NC | Open |
| ENG 1 | Observation | Open |
| ENG 2 | NC | Open |
| ENG 2 | Observation | Open |
| ENG 3 | NC | Open |
| ENG 4 | NC | Open |
can anyone help me with the measure for calculating the count.
hello WinterGarden
sorry, my bad. i didnt read your post thoroughly, with "Open" filter it does become 2.
so, here is the adjustment.
create a new measure with following DAX
NC Count =
var _Project = VALUES('Project'[Project Name])
Return
CALCULATE(
COUNTROWS('IDR'),
TREATAS(_Project,'IDR'[Project Name]),
FILTER(
'IDR',
'IDR'[Category]="NC"&&
'IDR'[Finding Status]="Open"
)
)as your requested,1. no relationship between Project and IDR.2.1. Lead slicer from Engagement tbl
2.2 Project slicer from Project tbl
i assumed there is still a relationship between engagement and project since you didnt mentioned it.or is this saying about no relationship between those three tabels?Hope this will help.
Thank you.
6 Replies
hello WinterGarden
is this what you are looking for?
create a new measure with following DAX
NC Count = CALCULATE(COUNTROWS('IDR'),'IDR'[Category]="NC")
also the count should be 3, not 2 as you mentioned above.As you said, Lead "A" will filter "ENG 1" and "ENG 2"."ENG 1" has 2 NC and "ENG 2" has 1 NC.So total NC for Lead "A" is 3.Hope this will help you.Thank you.Hi Irwan ,
Lead filter is from Engagement tracker table
Project Name in that table visual is from project Dimension table
i need to calculate the count of project name where category = NC and Finding Status = Open from IDR table.
Also there is no relationship between IDR table and project Dimension tablehello WinterGarden
sorry, my bad. i didnt read your post thoroughly, with "Open" filter it does become 2.
so, here is the adjustment.
create a new measure with following DAX
NC Count =
var _Project = VALUES('Project'[Project Name])
Return
CALCULATE(
COUNTROWS('IDR'),
TREATAS(_Project,'IDR'[Project Name]),
FILTER(
'IDR',
'IDR'[Category]="NC"&&
'IDR'[Finding Status]="Open"
)
)as your requested,1. no relationship between Project and IDR.2.1. Lead slicer from Engagement tbl
2.2 Project slicer from Project tbl
i assumed there is still a relationship between engagement and project since you didnt mentioned it.or is this saying about no relationship between those three tabels?Hope this will help.
Thank you.
