Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |