filter on 1 level of a matrix

[Hussein_charif]

i have a matrix with the following rows :
item,year,month,day.

i have a measure called difference % that gets me the difference % on item level and on year, month and day. the itemNo difference is calculated in a different way, here is the full dax code:

Difference % =

IF(

    COUNTROWS(VALUES(valueEntry[itemNo])) = 1 &&

    NOT(

        ISFILTERED(valueEntry[valuationDate]) ||

        ISFILTERED(valueEntry[year]) ||

        ISFILTERED(valueEntry[month]) ||

        ISFILTERED(valueEntry[day])

    ),

    VAR CurrentItemNo = MAX(valueEntry[itemNo])

    VAR MinDate =

        CALCULATE(

            MIN(valueEntry[valuationDate]),

            FILTER(

                ALL(valueEntry),

                valueEntry[itemNo] = CurrentItemNo

            )

        )

    VAR MaxDate =

        CALCULATE(

            MAX(valueEntry[valuationDate]),

            FILTER(

                ALL(valueEntry),

                valueEntry[itemNo] = CurrentItemNo

            )

        )

    VAR MinDateValue =

        CALCULATE(

            DIVIDE(

                SUM(valueEntry[Cost]),

                SUM(valueEntry[itemLedgerEntryQuantity]),

                0

            ),

            valueEntry[valuationDate] = MinDate

        )

    VAR MaxDateValue =

        CALCULATE(

            DIVIDE(

                SUM(valueEntry[Cost]),

                SUM(valueEntry[itemLedgerEntryQuantity]),

                0

            ),

            valueEntry[valuationDate] = MaxDate

        )

    VAR Difference = MaxDateValue - MinDateValue

    VAR itemdiff =

            DIVIDE(

            Difference,

            MinDateValue,

            0

        )

    RETURN

    itemdiff,

    DIVIDE(

        [Difference],

        [Average],

        0

    )

)

i want to filter another matrix in a bookmark to show only where the item difference % is >2.5%. how can i do it?

[v-linyulu-msft · Accepted solution]

Hi, @Hussein_charif 

As I am not entirely clear on your data, I have created the following example:

1.Firstly, you can use the following calculation columns:

Column =
IF (
    CALCULATE (
        'valueEntry'[Difference %],
        ALLEXCEPT ( 'valueEntry', 'valueEntry'[itemNo] )
    ) >= 0.25,
    1,
    0
)

2.Secondly, apply this calculated column to the visualisation object.  

3.Here's my final result, which I hope meets your requirements.

The final result may still need adjustments based on your data.Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

We recommend uploading it to GitHub and sharing the link with us.When uploading a file, please be careful to delete sensitive information.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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