Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
When I run Measure 1 and Measure 2 with a selection in [Field2], I get the following.
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:
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?
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:
Third column was not necessary.
Hi @DouweMeer,
It works well in my environment:
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.
Best Regards,
Link
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |