Only show rows when value is changed (based on date)

[TeisL]

Hi all,

I'm fairly new in the community and I'm struggling with this issue for over weeks now. I hope somebody can help me out:

I have a table with 4 columns:

* WKN = my ID

* AARDCONT = the value

* From = start of active value

* Till = stop of active value

I would like to retrieve the ID's who has been changed from 'BD' to 'OD' in a specific month (or with a specific date slicer)

I'm looking forward to a possible solution! Thanks in advance!

T

[TeisL]

It works better now, but there are still some wrong ID's in there.

example:

ID 3377 switched from BD to OD in 2017, so this one is not relevant anymore. We only want the ID if the value changed to OD within the datefilter.

[johnt75 · Accepted solution]

Needs REMOVEFILTERS

Row Is Visible = 
var bdValues = CALCULATETABLE( VALUES( 'data'[WKN]), REMOVEFILTERS(data), 'data'[AARDCONT] = "BD", USERELATIONSHIP('Calendar'[Date], 'data'[Till]) )
var odValues = CALCULATETABLE( VALUES('data'[WKN]), REMOVEFILTERS(data), 'data'[AARDCONT] = "OD")
return IF( SELECTEDVALUE( 'data'[WKN] ) IN INTERSECT( bdValues, odValues), 1, 0)

[TeisL]

I'm affraid this is not the solution. I'm having no result now:

[johnt75]

Is it possible to share a PBIX ?

[TeisL]

Hi @johnt75 ,

Thank you for your feedback and coorporation!

Unfortunately, I'm not quiet there...

The result is showing all the ID's where an OD started. I only need to see the ID's where the value changed from BD to OD.
Do you think this is feasible as well?

I added the calender:

Results:

Let me know if I should change something.
Thanks!

[johnt75]

Try

Row Is Visible =
var bdValues = CALCULATETABLE( VALUES( 'data'[WKN]), 'data'[AARDCONT] = "BD", USERELATIONSHIP('Calendar'[Date], 'data'[Till]) )
var odValues = CALCULATETABLE( VALUES('data'[WKN]), 'data'[AARDCONT] = "OD")
return IF( SELECTEDVALUE( 'data'[WKN] ) IN INTERSECT( bdValues, odValues), 1, 0)

[johnt75]

Firstly you'll need a Date table which is not connected to your fact table, for use in the slicer. If your main Date table is not connected then you can use that, otherwise you will need to create a new table just for use in the slicer.

When you have added the disconnected date table to a slicer on the page, you can create a measure like

Row Is Visible =
VAR minDate =
    MIN ( 'Date Slicer'[Date] )
VAR maxDate =
    MAX ( 'Date Slicer'[Date] )
VAR result =
    IF (
        (
            SELECTEDVALUE ( 'Table'[AARDCONT] ) = "BD"
                && SELECTEDVALUE ( 'Table'[Till] ) >= minDate
                && SELECTEDVALUE ( 'Table'[Till] ) <= maxDate
        )
            || (
                SELECTEDVALUE ( 'Table'[AARDCONT] ) = "OD"
                    && SELECTEDVALUE ( 'Table'[From] ) >= minDate
                    && SELECTEDVALUE ( 'Table'[From] ) <= maxDate
            ),
        1,
        0
    )
RETURN
    result

and add this measure as a visual filter to your table, to only show when the value is 1