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 Community.
Im working a project where I want to analyse if a given asset is in multiple portfolios.
The picture below illustrates what im trying to accomplish.
Can I write a measure that filters the table, so the resulting table consist of all the yellow rows?
Name of Portfolio and Asset Code is in two different tables.
The values is market value, which is a measure.
Hope someone can help me out.
Solved! Go to Solution.
[# Assets In 2+ Portfolios] =
SUMX(
// You have to iterate over
// a column that uniquely
// identifies Assets.
DISTINCT( Assets[AssetID] ),
CALCULATE(
// Same as above for Portfolios
1 * ( DISTINCTCOUNT( Portfolio[PortfolioID] ) > 1 ),
FILTER(
DISTINCT( Portfolio[PortfolioID] ),
[Market Value]
)
)
)
Use this measure in the Filter Pane to filter the rows of the visual. For one visible asset it should return 1 only if the asset is in at least 2 different portfolios (as visible in the current context, of course).
[# Assets In 2+ Portfolios] =
SUMX(
// You have to iterate over
// a column that uniquely
// identifies Assets.
DISTINCT( Assets[AssetID] ),
CALCULATE(
// Same as above for Portfolios
1 * ( DISTINCTCOUNT( Portfolio[PortfolioID] ) > 1 ),
FILTER(
DISTINCT( Portfolio[PortfolioID] ),
[Market Value]
)
)
)
Use this measure in the Filter Pane to filter the rows of the visual. For one visible asset it should return 1 only if the asset is in at least 2 different portfolios (as visible in the current context, of course).
It was exactly what I needed. Thanks for your assistance.
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 |
---|---|
39 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |