Filter on first matrix row only

[Hussein_charif]

Hi, i have a matrix that has the following rows :

ItemNo, year,month,day.

it has the following values:
description, average, difference, difference %.

and here is the difference% all measure :
Item and Aggregated Difference % =
IF(
COUNTROWS(VALUES(valueEntry[itemNo])) = 1 &&
NOT(
ISFILTERED(valueEntry[valuationDate]) ||
ISFILTERED(valueEntry[year]) ||
ISFILTERED(valueEntry[month]) ||
ISFILTERED(valueEntry[day])
),

-- Single itemNo logic
VAR CurrentItemNo = MAX(valueEntry[itemNo])

-- Dynamically calculate the earliest date for the current item
VAR MinDate =
CALCULATE(
MIN(valueEntry[valuationDate]),
FILTER(
ALL(valueEntry),
valueEntry[itemNo] = CurrentItemNo
)
)

-- Dynamically calculate the latest date for the current item
VAR MaxDate =
CALCULATE(
MAX(valueEntry[valuationDate]),
FILTER(
ALL(valueEntry),
valueEntry[itemNo] = CurrentItemNo
)
)

-- Calculate the value on the MinDate
VAR MinDateValue =
CALCULATE(
DIVIDE(
SUM(valueEntry[Cost]),
SUM(valueEntry[itemLedgerEntryQuantity]),
0
),
valueEntry[valuationDate] = MinDate
)

-- Calculate the value on the MaxDate
VAR MaxDateValue =
CALCULATE(
DIVIDE(
SUM(valueEntry[Cost]),
SUM(valueEntry[itemLedgerEntryQuantity]),
0
),
valueEntry[valuationDate] = MaxDate
)

-- Calculate the difference between the values
VAR Difference = MaxDateValue - MinDateValue

VAR itemdiff =
DIVIDE(
Difference,
MinDateValue,
0
)

-- Calculate % Difference for itemNo level
RETURN
itemdiff,

-- Aggregated logic (when multiple items or filters are applied)
DIVIDE(
[New Difference],
[Total AVG Cost all],
0
)
)
////

the main issue here is revolved around the difference % measure.

i made another measure, called item diff%:

item diff% =

SWITCH(
TRUE(),
ISINSCOPE(valueEntry[year]), -- Check if itemNo is in scope
BLANK(),
ISINSCOPE(valueEntry[itemNo]),
[Item and Aggregated Difference %]
)
///

to only get the difference on item level.

i did that to create a button called item >2.5%,
which takes me to bookmark page that has the exact same matrix, but in that matrix, i have a filter on it to only show values where the item diff% is >2.5%.

i used this filter measure :

ShowFilter 2.5 item =

VAR DiffPercentage = ABS([item diff%]) -- Absolute value of Difference %

RETURN

IF(NOT ISBLANK(DiffPercentage) && DiffPercentage > 0.025, 1, 0)
///

so, the problem is the following:

when i set the showfilter 2.5 item measure to : is 1
in the matrix filter panel,the matrix became all blank.

i was testing around, and found out that when i removed all the fields from the rows, except for the item No., the filter worked perfectly.

so the filter doesnt work if i have those fields in my rows..

what can i change to fix it?

[v-heq-msft]

Hi @Hussein_charif ,
Thanks for rajendraongole1 reply. 

Based on the code you provided, the problem may be in the context your measure is using, you can change the all function in the variable mindate to allselected function

VAR MinDate =
CALCULATE(
    MIN(valueEntry[valuationDate]),
    FILTER(
        ALLSELECTED(valueEntry),
        valueEntry[itemNo] = CurrentItemNo
    )
)

If the above modifications still can't solve your problem, you can provide the full example data, preferably a pbix file, so we can help you more accurately. Please hide sensitive information in advance.

Best regards,
Albert He

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

[rajendraongole1]

Hi @Hussein_charif -You can adjust your measure and filtering logic to account for the additional row context (year, month, day) in your matrix.

item diff% =
SWITCH(
TRUE(),
ISINSCOPE(valueEntry[year]) || ISINSCOPE(valueEntry[month]) || ISINSCOPE(valueEntry[day]),
BLANK(),
ISINSCOPE(valueEntry[itemNo]),
[Item and Aggregated Difference %]
)

Modify ShowFilter 2.5 item to ensure it works only at the ItemNo level

ShowFilter 2.5 item =
VAR DiffPercentage = ABS([item diff%]) -- Absolute value of Difference %

RETURN
IF(
HASONEVALUE(valueEntry[itemNo]) && NOT ISBLANK(DiffPercentage) && DiffPercentage > 0.025,
1,
0
)

With the updated ShowFilter 2.5 item, you can safely filter the matrix:

Open the Filters pane.
Add the ShowFilter 2.5 item measure as a filter to the matrix visual.
Set it to filter values where ShowFilter 2.5 item equals 1.

Hope this works please check

[Hussein_charif]

hello, that is returning all blanks in my matrix, even the rows disappear. i've tried many similar solutions as well they all give me blanks