Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DouweMeer
Post Prodigy
Post Prodigy

ALL seems not to be working as expected in a measure

I'm using in a measure a combined rankx, sumx and minx through dynamic table created with selectcolumns. However, in this structure, I can not use all since selectcolumns won't allow it. 

This one won't work:

 

selectcolumns ( all ( 'table'[Field] ) , "Name" , [Field] )

 

 So I've created as such:

 

Measure 1 = selectcolumns ( 'table' , "Name" , [Field] )
Measure 2 = Calculate ( [Measure 1] , all ( 'table'[Field2] ) )

 

Of course, Measure 1 doesn't work as it doesn't return a single value, but in my full measure of 46 lines, it does. 

Measure 1 = 
VAR Level_Breach = min ( [FieldX] )
VAR Level2 = min ( [FieldY] )
VAR Filtered_Table = 
SELECTCOLUMNS( 
     'Excel - Big output' 
     , "Stuff3" , 'Excel - Big output'[Field1]
     , "Qty2" , 'Excel - Big output'[Field2]
     )
VAR Sales = 
sumx ( Filtered_Table , [Qty2] * [Stuff3] )
VAR Rank_Bottom_Up = 
SELECTCOLUMNS( 
     Filtered_Table
     , "Qty3" , [Qty2]
     , "Stuff4" , [Stuff3]
     , "Rank Bottom" 
         , Rankx ( 
             Filtered_Table 
             , [Stuff3]
             , [Stuff3]
             , ASC  
             , Dense
             )
     )
VAR Rank_Bottom_Up2 = 
SELECTCOLUMNS( 
     Rank_Bottom_Up
     , "Qty4" , [Qty3]
     , "Sales" , [Qty3] * [Stuff4]
     , "Rank Bottom2" , [Rank Bottom]
     )
VAR Running_Total =
SELECTCOLUMNS( 
     Rank_Bottom_Up 
     , "Rank Bottom3" , [Rank Bottom]
     , "Running Total" 
         , VAR a1 = 
             SELECTCOLUMNS( filter ( Rank_Bottom_Up2 , [Rank Bottom2] <= [Rank Bottom] ) , "Sales" , [Sales] )
         VAR a2 = sumx ( a1 , [Sales] ) / Sales
         RETURN
             if ( a2 < Level2 , BLANK() , [Stuff4] )
     )
VAR Level_Value = Minx ( Running_Total , [Running Total] )
RETURN
Level_Value

When I run Measure 1 and Measure 2 without filters on [Field2], I get the following:

First visible column is Measure 1 and second visible column is Measure 2

Untitled3.png

When I run Measure 1 and Measure 2 with a selection in [Field2], I get the following.

Untitled.png

When I removed the filter from [Field2] and apply a manual filter as such:

Measure 2 = calculate ( [Measure 1] , [Field2] = "prior filter value" )

I get this:

Untitled2.png

 

Who could think of why the second picture in the second measure is not returning the values 25, 9.5, 9.5, 5.2 and 9.5?

8 REPLIES 8
AlexisOlson
Super User
Super User

First,

 

SELECTCOLUMNS ( ALL ( 'table'[Field] ), "Name", [Field] )

 

does work (as a calculated table) as long as you aren't trying to access any other column of the table other than the one inside the ALL. For that, you'd want something like

 

SELECTCOLUMNS (
    CALCULATETABLE ( 'table', ALL ( 'table'[Field2] ) ),
    "Name1", [Field1],
    "Name2", [Field2]
)

 

 

Second, the result you are getting where applying a slicer selection returns the same result as applying that same filtering manually within a measure is what I'd expect. Why do you expect it to behave differently?

This is weird. 

FieldA = Value A

FieldB = Year 2020

Field2 = customer name

No filter on [Field2]:

 

countrows ( 'table' )

 

Returns 6385 lines.

With filter on customer name [Field2], it returns 119 lines.

With filter on customer name,

 

countrows ( allexcept ( 'table' , 'table'[FieldA] , 'table'[FieldB] ) )

 

Returns 1.3 million lines. Why not 6385? 

Because with filter on customer name

 

countrows ( all ( 'table' ) )

 

returns 1.3 million lines as well. Why returns allexcept the same number?

With filter on customer name,

 

calculate ( countrows ( 'table' ) , allexcept ( 'table' , 'table'[FieldA] , 'table'[FieldB] ) )

 

Returns 154 lines. Why not 1.3 million and why not 6385?

With filter on customer name,

 

Measure 1 = countrows ( 'table' )
Measure 2 = calculate ( [Measure 1] , all ( 'table'[Field2] ) )

 

Measure 2 returns 154 lines. Again, why not 6385?

WIth filter on customer name,

 

countrows ( all ( 'table'[field2] ) )

 

returns 45k lines (apparently distinct count), why not 1.3 million? You would expect that

 

countrows ( distinct ( all ( 'table'[Field2] ) ) )

 

Would return the 45k. Not the one without the distinct...

 

Kill me. Where's the logic? Why is...

 

calculate ( countrows ( 'table' ) , all ( 'table'[Field2] ) )

 

the result 154 and not 6385?

I'm happy and confuesed. 

There was a filter on the price as well that it was not allowed to empty due to the weighted nature of the calculation. This caused 

calculate ( [Measure 1] , all ( 'table'[Field2] ) )

to fail. Even the advanced filter "is not blank" did not work. 

What did work, which I still don't understand why it does work, is to create a customn column with the statement

if ( [Field] = blank () , "Bad" , "Good" )

and then only filter the "Good" values. This does make it work as expect. 

If someone can explain why this works and why this is 'expected behavior', I'm willing to mark their comment as the solution over this one. 

Hi @DouweMeer,

 

Not sure if this is due to the sample data.

Could you provide pbix file after removing sensitive information?

I will try to check it in my environment.

 

Best Regards,

Link

No, can't do that :). 

I sense that the cause is due to the selection method. If you select a numerical value, only those values within the original context are used against the dataset. Basically it filters the context not just but based on excluding blanks, but including those values in its original context. Opposed to the text values there was just but bad/ good. A set price of 20 versus 10 is both good. But 10 is not 20 and if filtered on is not blank, then 10 won't show since only 20 is available. Basically applying inner join logic. 

But knowing this for sure is something I don't do. Is it something you know from experience?

Got it reproduced:

Untitled.png

Untitled2.png

Third column was not necessary.

Test1 =
VAR t1 = SELECTCOLUMNS( 'Table' , "Qty" , 'Table'[Column2] , "Product" , 'Table'[Column3] )
RETURN
countrows ( t1 )

Hi @DouweMeer,

 

It works well in my environment:

v-xulin-mstf_0-1618973290831.png

The pbix is attached, please check it.

 

Best Regards,

Link

 

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

Hey @v-xulin-mstf 

You wrote test2 different.

You wrote:

 

 

Test2 = 
CALCULATE(
    [Test1],
    ALL( 'Table'] )
)

 

 

But the all here should refer to only column1

 

 

Test2 = 
CALCULATE(
    [Test1],
    ALL( 'Table'[Column1] )
)

 

 

I was able to recreate my result where Test2 returns 2 instead of the expected 5. 

Hi @DouweMeer

 

What is your expected output?

10 won't show but only 20 because you apply a slicer.

v-xulin-mstf_0-1619079973964.png

 

Best Regards,

Link

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.