Allexcept behaves unexpected

[DouweMeer]

Created a measure, countrows (1), which is : countrows ( 'Table1' ). The created another measure, this time with allexcept, countrows (2). This is : countrows ( allexcept ( 'Table1' , 'Table1'[Product_Id] ) )

Then I created a table visual with Product_Id on the row and both measures as value columns. See picture below.

I expected the same results from both measures, but it doesn't. Anyone has a suggestion how to use allexcept in countrows (2) and get the same results as countrows (1) ? There are not other filters applied in the report.

[Icey]

Hi @DouweMeer ,

Create your [Countrows (2)] measure like so:

Countrows (2) =
COUNTROWS (
    FILTER ( 'Table1', 'Table1'[PRODUCT_ID] = MAX ( 'Table1'[PRODUCT_ID] ) )
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DouweMeer]

@Icey 

Thank you for reaching out. 

The information in the table filter already filters the context to only the product on the row. Therfore the "where" statement: 

'Table1'[PRODUCT_ID] = MAX ( 'Table1'[PRODUCT_ID] )

 Doesn't do anything. Can you perhaps explains what your attempt was?

[Icey]

Hi @DouweMeer ,

Sorry for missing "ALLSELECTED" in the above expression. Try this:

Countrows (2) =
COUNTROWS (
    FILTER (
        ALLSELECTED ( 'Table1' ),
        'Table1'[PRODUCT_ID] = MAX ( 'Table1'[PRODUCT_ID] )
    )
)

The expression below plays the same role with "ALLEXCEPT" in measures. 

'Table1'[PRODUCT_ID] = MAX ( 'Table1'[PRODUCT_ID] )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DouweMeer]

@Icey 

Strange to see that the behavior is to have...

MAX ( 'Table1'[PRODUCT_ID] )

 Calculated in the original context rather than the table reference created by the allselected. This would be ifferent compared to calculated columns... I thought you had to write it as follows:

VAR a1 = max ( 'Table1'[Product_Id] )

RETURN

countrows ( filter ( allselected ( 'Table1' ) , 'Table1'[Product_Id] = a1 ) )

Apparently not.

Went on writing that this wouldn't be a solution, however using the filter expression:

countrows (

filter ( allselected ( 'Table1' )

if ( hasonefilter ( 'Table1'[PRODUCT_ID] ) , 'Table1'[PRODUCT_ID] = max ( 'Table1'[PRODUCT_ID] ) , true () )

))

Seems to be working. Same as:

calculate ( countrows ( 'Table1' ) , allexcept ( 'Table1' , 'Table1'[PRODUCT_ID] ) )

Do you perhaps know why allexcept doesn't work without the calculate around it, but allselected does?

[Icey]

Hi @DouweMeer ,

Here are some examples explains the differences between ALLEXCEPT is used as a modifier in CALCULATE or CALCULATETABLE and ALLEXCEPT is used as a table function. Hope this could help you.

---

• When used as a modifier in CALCULATE or CALCULATETABLE, ALLEXCEPT removes the filters from the expanded table specified in the first argument, keeping only the filters in the columns specified in the following arguments.
• When used as a table function, ALLEXCEPT materializes all the unique combinations of the columns in the table specified in the first argument that are not listed in the following arguments. In this case, the result only has the columns of the table and ignores the expanded table.
•  However, ALLEXCEPT is commonly used as a CALCULATE modifier and very rarely needed as a table function.

---

For ALLSELECTED, you can refer to this document.

---

• ALLSELECTED can be used as a table expression when it has at least one argument.
• ALLSELECTED without arguments can be used only as a CALCULATE or CALCULATETABLE modifier and removes all the filters from the filter context.

---

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DouweMeer]

That's fun... Allexcept just becomes something completely different when used as a table reference for filter. 

[nandukrishnavs]

@DouweMeer 

Try this

CALCULATE(COUNTROWS(MyTable),ALLEXCEPT(MyTable,MyTable[ID]))

 

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[DouweMeer]

@nandukrishnavs 

Hmm, that does work... I wonder how that could be applied to the one below. Because calculate doesn't allow a table reference as return value... 

[nandukrishnavs]

@DouweMeer  Could you explain your real scenario? 

[DouweMeer]

@nandukrishnavs 

The final calculation is : ( Average Price This Year - Average Price Last Year ) x Quantity This Year

For the average prices, I have sales and quantities. So I created a table reference for this year and last year being VAR tTY and VAR tLY. Since you can't have an all () with just but columns as a table reference, I choose to use allexcept. 

I have it now as follows:

The code exploded with 3x more lines, but it seems to be working... So it looks like allexcept doesn't work nested in filter...

[nandukrishnavs]

@DouweMeer  

Use ALLEXCEPT() in side FILTER()

[DouweMeer]

@nandukrishnavs 

Doesn't work:

[nandukrishnavs]

@DouweMeer  I'm not getting your scenario. 

[DouweMeer]

@nandukrishnavs 

Perhaps the whole code makes more sense (124 lines):

M F Price YTD =

VAR a1 = minx ( 'Price Realization (2)' , [Year] )

VAR a2 = maxx ( filter ( all ( 'Price Realization (2)' ) , 'Price Realization (2)'[Year] = a1 ) , [Month] )

VAR zPPTY =

divide (

calculate (

sumx (

SELECTCOLUMNS(

filter ( 'Price Realization (2)'

, 'Price Realization (2)'[Year] = a1

&& 'Price Realization (2)'[Month] <= a2

)

, "FXN2" , 'Price Realization (2)'[FXN sale EUR]

, "Qty2" , 'Price Realization (2)'[SumOfQTY]

)

, [FXN2]

)

, allexcept ( 'Price Realization (2)'

, 'Price Realization (2)'[Business Group]

, 'Price Realization (2)'[SALESREP_NAME]

, 'Price Realization (2)'[REGION_TERR]

, 'Price Realization (2)'[PRODUCT_ID]

, 'Price Realization (2)'[COUNTRY_CD]

, 'Price Realization (2)'[DIST_NAME_ABBR]

, 'Price Realization (2)'[Maintained Future]

)

)

, calculate (

sumx (

SELECTCOLUMNS(

filter ( 'Price Realization (2)'

, 'Price Realization (2)'[Year] = a1

&& 'Price Realization (2)'[Month] <= a2

)

, "FXN2" , 'Price Realization (2)'[FXN sale EUR]

, "Qty2" , 'Price Realization (2)'[SumOfQTY]

)

, [Qty2]

)

, allexcept ( 'Price Realization (2)'

, 'Price Realization (2)'[Business Group]

, 'Price Realization (2)'[SALESREP_NAME]

, 'Price Realization (2)'[REGION_TERR]

, 'Price Realization (2)'[PRODUCT_ID]

, 'Price Realization (2)'[COUNTRY_CD]

, 'Price Realization (2)'[DIST_NAME_ABBR]

, 'Price Realization (2)'[Maintained Future]

)

)

, BLANK()

)

VAR zPPLY =

divide (

calculate (

sumx (

SELECTCOLUMNS(

filter ( 'Price Realization (2)'

, 'Price Realization (2)'[Year] = a1 -1

&& 'Price Realization (2)'[Month] <= a2

)

, "FXN2" , 'Price Realization (2)'[FXN sale EUR]

, "Qty2" , 'Price Realization (2)'[SumOfQTY]

)

, [FXN2]

)

, allexcept ( 'Price Realization (2)'

, 'Price Realization (2)'[Business Group]

, 'Price Realization (2)'[SALESREP_NAME]

, 'Price Realization (2)'[REGION_TERR]

, 'Price Realization (2)'[PRODUCT_ID]

, 'Price Realization (2)'[COUNTRY_CD]

, 'Price Realization (2)'[DIST_NAME_ABBR]

, 'Price Realization (2)'[Maintained Future]

)

)

, calculate (

sumx (

SELECTCOLUMNS(

filter ( 'Price Realization (2)'

, 'Price Realization (2)'[Year] = a1 -1

&& 'Price Realization (2)'[Month] <= a2

)

, "FXN2" , 'Price Realization (2)'[FXN sale EUR]

, "Qty2" , 'Price Realization (2)'[SumOfQTY]

)

, [Qty2]

)

, allexcept ( 'Price Realization (2)'

, 'Price Realization (2)'[Business Group]

, 'Price Realization (2)'[SALESREP_NAME]

, 'Price Realization (2)'[REGION_TERR]

, 'Price Realization (2)'[PRODUCT_ID]

, 'Price Realization (2)'[COUNTRY_CD]

, 'Price Realization (2)'[DIST_NAME_ABBR]

, 'Price Realization (2)'[Maintained Future]

)

)

, BLANK()

)

VAR zQtyTY =

calculate (

sumx (

SELECTCOLUMNS(

filter ( 'Price Realization (2)'

, 'Price Realization (2)'[Year] = a1

&& 'Price Realization (2)'[Month] <= a2

)

, "FXN2" , 'Price Realization (2)'[FXN sale EUR]

, "Qty2" , 'Price Realization (2)'[SumOfQTY]

)

, [Qty2]

)

, allexcept ( 'Price Realization (2)'

, 'Price Realization (2)'[Business Group]

, 'Price Realization (2)'[SALESREP_NAME]

, 'Price Realization (2)'[REGION_TERR]

, 'Price Realization (2)'[PRODUCT_ID]

, 'Price Realization (2)'[COUNTRY_CD]

, 'Price Realization (2)'[DIST_NAME_ABBR]

, 'Price Realization (2)'[Maintained Future]

)

)

VAR a3 = ( zPPTY - zPPLY ) * zQtyTY

RETURN

a3