Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |