Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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)
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.
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)
I'm affraid this is not the solution. I'm having no result now:
Is it possible to share a PBIX ?
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!
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)
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |