topic Re: Count of parents for which all the childs are compliant in DAX Commands and Tips

Count of parents for which all the childs are compliant

Aksh_12345 — Mon, 06 May 2024 14:51:40 GMT

I am stuck in a problem for last few days. Actually I have a table in which I have few companies and their parent companies. Apart from this I have another table in which I have few parameters. So these are two dimensional tables and I have a fact table in which I have the information of each company and their complaints status for the parameters. Now I had two requirements, 1. I wanted to have The count of the company's which are Compliant for all the available parameters and I have created a measure for that Which is working completely fine. 2. I want to see the count of parent companies For which all the childs are complaint against every parameter that is available in the dim table. This measure is somehow not working properly. I am attaching a Power BI file for your reference. Please help me. Link- https://drive.google.com/file/d/1M7OewB9a1hXq9z4vl5VqwEGqS94at5t_/view?usp=drivesdk Let me know if there is any confusion PS-- this was not a relationship problem, because I had the relationship created between the dim and fact tables but removed it for some testing and trying out few logics. Also, I tried ISEMPTY() function using NC as a condition, but I can't use that. Reason is that we have C and NC both available for same company same parameter and same period and in such case we consider C for that company for that period. Would really appreciate the quick support. Thanks

Re: Count of parents for which all the childs are compliant

lbendlin — Mon, 06 May 2024 20:19:28 GMT

Like this?

Re: Count of parents for which all the childs are compliant

Aksh_12345 — Tue, 07 May 2024 03:29:23 GMT

Hey lbendlin,

Thanks for your response. But I just want to get the count of such parent companies for which all sub companies are compliant for all parameters. And just one more point, we can't say whether any company has entries for all the parameters in the fact table. So, we need to compare the number of entries for each child in the fact with the distinctcount of parameters in dimparameters. I am doing something similar in my file but somehow it is not working. Could you have a look at that?

Waiiting for your response

Regards

Re: Count of parents for which all the childs are compliant

lbendlin — Tue, 07 May 2024 11:33:22 GMT

I don't understand what you are trying to achieve. Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Re: Count of parents for which all the childs are compliant

Aksh_12345 — Tue, 07 May 2024 11:51:14 GMT

Hey lbendlin ,

Sorry for the confusion, based on the sample data, the expected outcome is 1. I just want to create a card with number of parents compliant.

Just one condition that I have is that we can't use NC entries in any of the filters in DAX.

Re: Count of parents for which all the childs are compliant

lbendlin — Tue, 07 May 2024 12:14:05 GMT

Just one condition that I have is that we can't use NC entries in any of the filters in DAX.

That makes it impossible to implement a solution.

Re: Count of parents for which all the childs are compliant

Aksh_12345 — Tue, 07 May 2024 12:40:43 GMT

Thats what it looks like. I think I need to create a calculated table first after filtering the NC entries and add a calculated column there which can do a count of parameters for each company individually. Then based on that column I can do parent level calculation as well in the measure