topic Re: Only show rows when value is changed (based on date) in DAX Commands and Tips

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

TeisL — Mon, 20 Jun 2022 09:43:27 GMT

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!

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

johnt75 — Mon, 20 Jun 2022 09:55:55 GMT

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

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

TeisL — Mon, 20 Jun 2022 12:57:52 GMT

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!

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

johnt75 — Mon, 20 Jun 2022 13:07:32 GMT

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)

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

TeisL — Mon, 20 Jun 2022 13:25:09 GMT

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

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

johnt75 — Mon, 20 Jun 2022 13:29:52 GMT

Is it possible to share a PBIX ?

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

johnt75 — Mon, 20 Jun 2022 14:26:14 GMT

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)

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

TeisL — Mon, 20 Jun 2022 14:54:49 GMT

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.