Forum Discussion
Post PartisanQuestion on field parameters and DAX measure
Hello,
I am using field parameters in a table with an added columns like this:
Portfolio_Matrix_Field_Choice = {
("Definition 1", NAMEOF('ProjectID_Key'[Definition 1]), 0,"Definition A"),
("Definition 2", NAMEOF('ProjectID_Key'[Definition 2]), 2,"Definition B"),
("Definition 3", NAMEOF('ProjectID_Key'[Definition 3]), 3,"Definition C"),
("Definition 4", NAMEOF('ProjectID_Key'[Definition 4]),1,"Definition D"),
("Definition 5", NAMEOF('ProjectID_Key'[Definition 5]),4,"Definition E"),
("Definition 6", NAMEOF('ProjectID_Key'[Definition 6]), 5,"Definition F"),
("Definition 7", NAMEOF('ProjectID_Key'[Definition 7]),6,"Definition G")
}
this is working well.
The last column of my field parameters table (called Portfolio_Matrix_Field_Choice[Columns]) has the exact same names as the tablecolumn I use to explain my report columns and measures (Measures_Columns_Definitions[DisplayName]). So I would like to retrieve the information about the description of the fields and show it when the users select a field in the field parameters slicer.
Now I have this measure:
I don't understand why it works without issues only when both fields of my field parameters table are called the same as the entries in Measures_Columns_Definitions[DisplayName], for ex. like so
("Definition A", NAMEOF('ProjectID_Key'[Definition 1]), 0,"Definition A"),
I would have thought that with TREATAS the system was only going to be looking at the correspondance between
Portfolio_Matrix_Field_Choice[Columns]
and
Measures_Columns_Definitions[DisplayName],
why is the first columns of the table Portfolio_Matrix_Field_Choice impacting the measure?
I hope I was clear - thanks a lot for your help!
Kind regards
Valeria
Hi ValeriaBreve,
You're right to think that TREATAS should only transfer the filter context from Portfolio_Matrix_Field_Choice[Columns] to Measures_Columns_Definitions[DisplayName]. However, the issue arises due to how Field Parameters work in Power BI. Here's what is happening:
Field parameters in Power BI are special tables that dynamically control which column or measure is displayed in a visual. When you create a field parameter, Power BI creates an internal relationship between:
- The first column (user-friendly name shown in the slicer)
- The second column (NAMEOF(...)) that represents the actual column reference in your dataset
Even though your TREATAS function is using Portfolio_Matrix_Field_Choice[Columns], Power BI still inherits the implicit filtering from the first column in the field parameter table. This means that when users select a field in the slicer, Power BI is automatically filtering not just Portfolio_Matrix_Field_Choice[Columns], but also the first column in the parameter table.
If the names do not match, the filtering does not work as expected because Portfolio_Matrix_Field_Choice[Columns] is still indirectly influenced by the selection of the first column.
You can try below DAX to overcome this:-
# Metric Definition =
CALCULATE(
MAX(Measures_Columns_Definitions[Definition]),
TREATAS(
VALUES(Portfolio_Matrix_Field_Choice[Columns]),
Measures_Columns_Definitions[DisplayName]
),
REMOVEFILTERS(Portfolio_Matrix_Field_Choice) -- Removes unintended filters
)🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!
2 Replies
- grazitti_sapna
Super User
Hi ValeriaBreve,
You're right to think that TREATAS should only transfer the filter context from Portfolio_Matrix_Field_Choice[Columns] to Measures_Columns_Definitions[DisplayName]. However, the issue arises due to how Field Parameters work in Power BI. Here's what is happening:
Field parameters in Power BI are special tables that dynamically control which column or measure is displayed in a visual. When you create a field parameter, Power BI creates an internal relationship between:
- The first column (user-friendly name shown in the slicer)
- The second column (NAMEOF(...)) that represents the actual column reference in your dataset
Even though your TREATAS function is using Portfolio_Matrix_Field_Choice[Columns], Power BI still inherits the implicit filtering from the first column in the field parameter table. This means that when users select a field in the slicer, Power BI is automatically filtering not just Portfolio_Matrix_Field_Choice[Columns], but also the first column in the parameter table.
If the names do not match, the filtering does not work as expected because Portfolio_Matrix_Field_Choice[Columns] is still indirectly influenced by the selection of the first column.
You can try below DAX to overcome this:-
# Metric Definition =
CALCULATE(
MAX(Measures_Columns_Definitions[Definition]),
TREATAS(
VALUES(Portfolio_Matrix_Field_Choice[Columns]),
Measures_Columns_Definitions[DisplayName]
),
REMOVEFILTERS(Portfolio_Matrix_Field_Choice) -- Removes unintended filters
)🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!
ValeriaBreveThank you grazitti_sapna ! very clear and the removefilters works very well. I had not thought of the internal relationships created by field parameters. Thanks again!