Dax/Measure for calculating the count based on filters applied in another table

[WinterGarden]

Hi,
I've 2 tables , 
Engagement details:

Engagement Name	Lead
ENG 1	A
ENG 2	A
ENG 3	B
ENG 4	C

Project Summary:

Engagement 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

I've a filter/slicer ie. Lead(from Engagement details table) in the page.
i need to calculate the count for each engagements where Category = NC and Finding Status = Open.
while clicking each Engagement Name in the table view , i need to show the count in a card.
basically need a measure which filters Project Summary table based on  below conditions:
1)Engagement Name = values filtered from Engagement details where lead = selected value
2)Category = NC and Finding Status = Open.
can anyone help me with the measure for calculating the count.

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

expected result measure: = 
COUNTROWS(
    FILTER('Project summary',
    'Project summary'[Category] = "NC" &&
    'Project summary'[Finding Status] = "Open"
    )
)

[shafiz_p]

Hi @WinterGarden  Considering the relationship Engagement details to Engagement summary. Please create the below measure to calculate count of Engagement.

Count of Engagement =

COUNTROWS(

    FILTER('Engagement Summary',

    'Engagement Summary'[Category] = "NC" &&

    'Engagement Summary'[Finding Status] = "Open"

    )

)

Hope this helps!!

If this solved your problem, please accept it as a solution!!

Best Regards,
Shahariar Hafiz

[WinterGarden]

Hi @shafiz_p ,
this wont work in my case beacuse..
i am displaying the "Engagement Name" from Engagement details table along with few other colums from another table in a table visual.
Also i cant create direct relationship between these two tables as it cause ambiguity issue.
my ask is , if i selected lead = A, then the Engagement Names= ENG 1, ENG 2.
now i need to show the count ENG 1, ENG 2 where Category = NC and Finding Status = Open.
i need a measure which filters Project Summary table based on  below conditions:
1)Engagement Name = values filtered from Engagement details where lead = selected value
2)Category = NC and Finding Status = Open.

[shafiz_p]

@WinterGarden  This is perfectly working for me. See the image below:

See the relationship also.

This is based on your provided data. Since you are faceing problem, then I would suggest you to provide complete picture, so that community can help you.

Hope this helps.

[WinterGarden]

Hi @shafiz_p/@Jihwan_Kim  ,
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

[shafiz_p]

@WinterGarden  I think you have problem with your relationship. You are counting, aggregating all from IDR table but relationship direction as per your statement "IDR(Project Name) to Engagement tracker(Project Name)". Also you don't have any relationship between Project dimention table to IDR table. So if you filter anything from Engagement tracker, it would not propagate the IDR table. 

I would suggest Identify your dimension and fact table, and create relationship.

[Anonymous · Accepted solution]

Hi @WinterGarden ,

If you cannot create a relationship between tables because of relationship ambiguity.

You might try the TREATAS function:

MEASURE = 
CALCULATE (
    COUNTROWS ( 'Project Summary' ),
    'Project Summary'[Category] = "NC"
        && 'Project Summary'[Finding Status] = "Open",
    TREATAS (
        VALUES ( 'Engagement details'[Engagement Name] ),
        'Project Summary'[Engagement Name]
    )
)

TREATAS - DAX Guide

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum