Filter lines with CountDistinct at least x

[josef_kuenzli]

Hi

I have input data with CreditExposure per ParentCompany and Subsidiary. The Subsidiary is always filled, the Parent may be empty (NULL). I´d like to show the Details of the Credit Exposure lended to a Parent with at least two subsidiaries. Hence, cases where Parent-Subsidiary is 1-1, I don´t want to show. Neither for cases where Parent is empty. I only want to show cases of one parent with several subsidiaries with Credit Exposure on each of them. 

I believe in SQL with a sub-query, it would be something like this: 

Select *

From

(Select

parent, count distinct(subsidiary), Credit Exposure

Group by Parent)

where count distinct(subsidiary)>=2

Input Data

Parent	Subsidiary	Credit Exposure
NULL	a	100
A	b	150
A	c	180
B	d	120

Expected Output Data

Parent	Subsidiary	Credit Exposure
A	b	150
A	c	180

Is that possible to do with DAX? 

Thanks a million 

Josef

[v-xicai · Accepted solution]

Hi @josef_kuenzli ,

You can create measure Filter1 like DAX below, then put the Filter1 in the Visual Level Filter of table visual, setting Filter1 as "is not blank".

Filter1 =
var count=CALCULATE(DISTINCTCOUNT(Table1[Subsidiary]),FILTER(ALLSELECTED(Table1), Table1[Parent] =MAX(Table1[Parent])))
return
IF(MAX(Table1[Parent])<>BLANK()&&count>=2, 1, BLANK() )

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[josef_kuenzli]

@v-xicai That was brilliant. Thank you!