Measure for filtering a table based on filtered values from another table

[WinterGarden]

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.

[Irwan]

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.

[WinterGarden]

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 table

[Irwan · Accepted solution]

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.

[WinterGarden]

Hi @Irwan ,
yes,These are the relationship that are created between those 3 tables:
1) Engagement tracker(Project Name) ---- project Dimension(Project Name)
2) IDR(Project Name) --- Engagement tracker(Project Name)

but there is no relationship between project Dimension & IDR

[Irwan]

hello @WinterGarden 

i am not sure i am following your description? dont understand --- mean..

but is the DAX working for you?

Thank you.

[WinterGarden]

@Irwan ,
Thank you so much. It is working fine !!