Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
josef_kuenzli
Regular Visitor

Filter lines with CountDistinct at least x

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

ParentSubsidiaryCredit Exposure
NULLa100
Ab150
Ac180
Bd120

 

Expected Output Data

ParentSubsidiaryCredit Exposure
Ab150
Ac180

 

Is that possible to do with DAX? 

Thanks a million 

Josef

1 ACCEPTED SOLUTION
v-xicai
Community Support
Community Support

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() )

50.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards,

Amy

 

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

View solution in original post

2 REPLIES 2
v-xicai
Community Support
Community Support

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() )

50.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards,

Amy

 

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

@v-xicai That was brilliant. Thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.