Forum Discussion
HASONEVALUE-Function shall only be working when a filter is set for a column in the query editor
Hello,
I'm struggling with a filtering issue for days and at the moment I'm not sure if it is feasible:
I want to display a warning by means of the IF-function, which vanishes if the Column "MyChoice" is filtered for one value in the query editor. For more than one value in the query editor's filter for "MyChoice" and for any active filter in the report, the warning shall be displayed. With the following function, I get something similar, but I cannot make the filter only valid for filters in the query editor.
IF(HASONEVALUE('MyTable'[MyChoice]); ""; "WARNING: The calculations are only valid if you have selected one value via the query editor! Please select one value!")
Is this possible or is there another alternative to achieve this kind of filter? I have the feeling that Power BI does not distinguish between filtering on report level or in the query editor and thus cannot be separated.
Thanks in advance, any hints and tricks will be appreciated!
xuri
Hey,
I'm not sure what you are referring to, if you mention query editor.
If you mean the query editor where you access your source data and prepare the data for its usage in the Power BI data model, you can not hand down a slicer selection to the query editor:
Basically there are these layers of interaction
Interaction Layer 1: Source Data (Data Layer 1) -> Power Query -> Data Model (Data Layer 2)
Interaction Layer 2: Data Model (Data Layer 2) -> Relationships / Calculated Columns / Measures -> Refined Data Model (Data Layer 3)Basically there are just these possibilities for a user to interact with the different Layers of Interaction:
IL1: Query Parameter
IL2: Slicer Selection / Cross Filtering / What-if parameter (since the August 2017 release).
If I'm getting you wrong, and your were referring to this DAX thing, you have to encapsulate each of your measures like this
theMeasure =
IF(HASONEVALUE( ... )
,the calcualtion of the measure
,the warning
)Hope this helps somehow
2 Replies
- TomMartens
Super User
Hey,
I'm not sure what you are referring to, if you mention query editor.
If you mean the query editor where you access your source data and prepare the data for its usage in the Power BI data model, you can not hand down a slicer selection to the query editor:
Basically there are these layers of interaction
Interaction Layer 1: Source Data (Data Layer 1) -> Power Query -> Data Model (Data Layer 2)
Interaction Layer 2: Data Model (Data Layer 2) -> Relationships / Calculated Columns / Measures -> Refined Data Model (Data Layer 3)Basically there are just these possibilities for a user to interact with the different Layers of Interaction:
IL1: Query Parameter
IL2: Slicer Selection / Cross Filtering / What-if parameter (since the August 2017 release).
If I'm getting you wrong, and your were referring to this DAX thing, you have to encapsulate each of your measures like this
theMeasure =
IF(HASONEVALUE( ... )
,the calcualtion of the measure
,the warning
)Hope this helps somehow
TomMartens: Thanks for your quick response! Yes, I actually meant the query editor where you can access your source data. So my assumption was correct, that it is not possible. I will start a new topic with my actual problem since this would be an extension of my question to which you have already given me the answer.
