Distinct Count of Parent Items from multiple child

[mshakir164]

I have a table named Customer with the columns Customerkey, ParentCustomer, and second table named FactCustomer with these columns Customerkey, Month, Status, and Confidence the relationship between these two is one to many on Customerkey I want to get a distinct count of ParentCustomer from Customer table where Status in FactCustomer table is close and confidence is max.

Note: There are multiple records in FactCustomer under the same ParentCustomer as Close in the Status column and with different values in the Confidence column

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[mshakir164]

@lbendlin 

I have a slicer of ParentCustomer, currently if I select A from ParentCustomer slicer then it shows me three rows in my required output table/grid and total as 1 I want only one row (the Red one) in the required output table/grid because from CustomerID 2,3, and 4 which are close and under same ParentCustomer I need only one where confidence is 96.

[Ashish_Mathur]

Hi,

Share the 2 tables in a format that can be pasted in an MS Excel file.

[mshakir164]

Customer		FactCustomer
CustomerID	ParentCustomer		CustomerID	Status	Month	Confidence	Zone
1	A		1	Active	20230101	78
2	A		2	Close	20230101	96	Red
3	A		3	Close	20230101	85	Yellow
4	A		4	Close	20230101	71	Green
5	B		5	Active	20230101	74
6	B		6	Close	20230101	54	Green
7	B		7	Close	20230101	85	Yellow
8	D		8	Active	20230101	65

Required Output
Zone	No of Closed Parent Customer
Red	1
Yellow	1
Green

[Ashish_Mathur]

Hi,

If you only want to show the Zone which has the highest number, then only Red should appear.  Why have you shown Yellow and Green in the result?

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...