Forum Discussion
Issue with P&L Dashboard – Dynamic Measure Calculation
Hi PBI Community,
I’ve encountered an issue while preparing a P&L dashboard.
I have a matrix with a parameter that switches between three dimensions: P&L Category (OP, NR, etc.), Region, and Product. Additionally, there is a reporting_metric slicer (single select) from the fact table.
Here is the logic I want to achieve:
- When P&L Category is selected, all P&L metrics should be displayed (ignoring the reporting_metric slicer).
- When Region or Product is selected, the calculation should reflect the P&L metric chosen from the reporting_metric slicer.
The problem is that when I select P&L Category, only one measure is displayed, limited by the reporting_metric slicer context. Despite my attempts (including assistance from AI models like Claude 3.7, o1, and o3), I keep running into the same issue.
Does anyone have an idea on how to solve this problem and achieve the desired behavior?
here it's one of measures that i tried to solve it with AI
___test ac =
VAR chosenDim = SELECTEDVALUE('ParameterP&LMatrix'[Type])
VAR chosenMetric = SELECTEDVALUE('FactP&L'[reporting_metric])
RETURN
SWITCH(
TRUE(),
// If P&L Category is selected, show all metrics (ignoring the reporting_metric slicer)
chosenDim = "P&L Category",
[Total AC],
// If Region or Product is selected, filter based on the selected reporting_metric from the slicer
chosenDim IN { "Region", "Product" },
CALCULATE(
[Total AC],
'FactP&L'[reporting_metric] = chosenMetric
),
BLANK()
)
Best regards,
Szymon
6 Replies
Simonn97_PBI , Make sure ParameterP&LMatrix is disconnected table for this case
___test ac =
VAR chosenDim = Values('ParameterP&LMatrix'[Type])
VAR chosenMetric = Values('FactP&L'[reporting_metric])
RETURN
SWITCH(
TRUE(),
// If P&L Category is selected, show all metrics (ignoring the reporting_metric slicer)
"P&L Category" in chosenDim,
[Total AC],
// If Region or Product is selected, filter based on the selected reporting_metric from the slicer
Countrow(INTERSECT( chosenDim , { "Region", "Product" })) >0,
CALCULATE(
[Total AC],
'FactP&L'[reporting_metric] in chosenMetric
),
BLANK()
)Unfortunately, it still doesn't work. The parameter also includes additional dimensions such as division, cluster, product group, etc., but I don't think these are causing the issue. I'm wondering if a better solution would be to base the reporting_metric slicer on a disconnected table and build the calculation using that in the model. What do you think?
Hi Simonn97_PBI ,
Thank you for reaching out to us on the Microsoft Fabric Community Forum.
Here’s why it helps:
- When your slicer is connected directly to your fact table, it automatically filters your data, even when you don’t want it to like when you're trying to show all P&L metrics for the "P&L Category" view.
- By switching to a disconnected table (a separate table with all the reporting metrics but no relationship to your model), the slicer won't filter anything by default.
- Then, in your DAX measure, you can manually apply the filter only when you need it ,such as when the selected dimension is "Region" or "Product" and ignore it when the selected dimension is "P&L Category."
This gives you full control over when and how the reporting metric gets applied.
If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.
Thank you,
Menaka.
