Forum Discussion
Autoexist in single column??
I have a model with just two tables: Table "Product" that contains the products names and table "Steps" that contains a row per production phase/step which are represented by a number. Not all products start in the same production step, some start in step 2 like product B. The tables are linked by the product column in a relation ship 1:*
I need to calculate which is the first production step for each product and show it in all the steps of the product in a table visual like this:
Desired result:
I select product B in a filter outside the table visual and in the table shoud display steps 1 and 2 of product B and the column with the measure "First Step" should displays always the number 2 which is the first step of product B.
To try achieve the above, I created the following DAX code:
First Step =
CALCULATE(
MIN(Steps[Step]),
REMOVEFILTERS(Steps[Step])
)and
First Step =
CALCULATE(
MIN(Steps[Step]),
ALLSELECTED()
)and in both cases I get the following result:
Why does the steps for other products appear in the table? Why is the table visual ignoring the filter on the Product when selecting the rows to display?
I thought the absence of autoexist behaviour occurs only when columns in the visual where from different tables, not when columns where from a single table (and a single column in the visual).
How can I get the desired result? (I just need those two columns in the table, no product in the table visual)
Thank you for the explanation.
Hello,
The behavior you are observing—where the table visual displays rows that should be filtered out—is due to how the First_Step measure interacts with the Filter Context.
Why is this occurring?
Because your visual contains only the Steps[Steps] column and your current measure uses ALLSELECTED() or REMOVEFILTERS().You are explicitly instructing Power BI to ignore the specific row filter.
When a measure returns a non-blank value (in this case, the value "2") for every potential row in the Steps table, Power BI identifies those rows as relevant to the visual. This "forces" the display of rows (such as steps 1 and 4) that would otherwise be excluded, as they now possess an associated value within that context.
Recommended Solution
To achieve your desired result without adding the Product column to the visual, you can modify your measure to include a check for the current context. This ensures the calculation only returns a value if the step is natively relevant to the current filter selection.
Proposed DAX Measure:
Code snippet
First_Step = IF( NOT ISBLANK(SELECTEDVALUE(Steps[Steps])), CALCULATE( MIN(Steps[Steps]), ALLSELECTED(Steps) ), BLANK() )Summary of Change
- With this modification: The IF statement ensures that if a step is filtered out by the Product slicer, the measure returns a BLANK(), allowing the visual to hide those rows automatically.
- Alternative: If the Steps[Product] column were present in the table visual, the existing measure would likely have functioned as expected due to the additional granularity in the filter context.
5 Replies
Add a visual-level filter using this measure:
Is Current Product = IF ( COUNTROWS ( Steps ) > 0, 1, BLANK () )Hello,
The behavior you are observing—where the table visual displays rows that should be filtered out—is due to how the First_Step measure interacts with the Filter Context.
Why is this occurring?
Because your visual contains only the Steps[Steps] column and your current measure uses ALLSELECTED() or REMOVEFILTERS().You are explicitly instructing Power BI to ignore the specific row filter.
When a measure returns a non-blank value (in this case, the value "2") for every potential row in the Steps table, Power BI identifies those rows as relevant to the visual. This "forces" the display of rows (such as steps 1 and 4) that would otherwise be excluded, as they now possess an associated value within that context.
Recommended Solution
To achieve your desired result without adding the Product column to the visual, you can modify your measure to include a check for the current context. This ensures the calculation only returns a value if the step is natively relevant to the current filter selection.
Proposed DAX Measure:
Code snippet
First_Step = IF( NOT ISBLANK(SELECTEDVALUE(Steps[Steps])), CALCULATE( MIN(Steps[Steps]), ALLSELECTED(Steps) ), BLANK() )Summary of Change
- With this modification: The IF statement ensures that if a step is filtered out by the Product slicer, the measure returns a BLANK(), allowing the visual to hide those rows automatically.
- Alternative: If the Steps[Product] column were present in the table visual, the existing measure would likely have functioned as expected due to the additional granularity in the filter context.
So this has nothing to do with auto exist behaviour?
I found in an article of SQLBI by AlbertoFerrari that SUMMARIZECOLUMNS internally converts this:EVALUATE SUMMARIZECOLUMNS ( TREATAS ( { "DAX", "Python" }, 'Projects'[Language] ), TREATAS ( { 2018 }, 'Projects'[Year] ), "Result", [# Projects All Time] )in to this:
EVALUATE SUMMARIZECOLUMNS ( CALCULATETABLE ( SUMMARIZE ( Projects, 'Projects'[Language], 'Projects'[Year] ), TREATAS ( { "DAX", "Python" }, 'Projects'[Language] ), TREATAS ( { 2018 }, 'Projects'[Year] ) ), "Result", [# Projects All Time] )so in my scenario, the Product column from the Products table is cross joined with Step column disabling auto exist and therefore evaluating the measure in all possible combination of Product and Step causing the behavior I'm seeing.
Is this correct? or I'm mixing up things?
Yes, this behavior is not related to auto-exist.
Auto-exist is applied only when multiple columns from the same table are used together as filters.However, in your scenario, the Product column originates from the Products table, while the Step column comes from the Steps table. Since these columns belong to different tables, auto-exist is not triggered. Instead, a cross-join is performed between these columns, generating all possible combinations.
This aligns with the explanation provided in the SQLBI article you referenced.
you can try this
First Step =if(max(steps[product])<>max('product'[product]),blank(),CALCULATE(MIN( Steps[Step] ),FILTER(ALL(steps),steps[product]=max('product'[product]))))
