Forum Discussion
Helper VFinding column values based on two different Values of same table
Community SupportHi PBILover ，
First create a slicer table,using below dax expression;
slicer table = DISTINCT('Table'[EventType])
Then crearte 2 measures as below:
_ID =
var _id=UNION( CALCULATETABLE(VALUES('Table'[Id]),FILTER(ALLSELECTED('Table'), 'Table'[EventType] in VALUES('slicer table'[EventType]))),CALCULATETABLE(VALUES('Table'[cid]),FILTER(ALLSELECTED('Table'), 'Table'[EventType] in VALUES('slicer table'[EventType])))) return
IF(SELECTEDVALUE('Table'[Id]) in _id,MAX('Table'[Id]),BLANK())Datediff =
var date1=MINX(FILTER(ALL('Table'),'Table'[EventType]=MAX('Table'[EventType])),'Table'[Date])
Return
CALCULATE(DATEDIFF(date1,MAX('Table'[Date]),DAY),ALLEXCEPT('Table','Table'[EventType]))
Finally you will see:
For the related .pbix file.pls click here.
Best Regards,
Kelly
PBILoverv-kelly-msft y Thank you very much for your detailed steps.
There was a slight change in the first step , as i dont want to create a slicer table, i appllied the filter on 'Table' as where event Type = 'a' then pick the cid and id of the related documnets and then again apply those ids filter on the result Table at the time of the creation of the measure _id.(As i want to consider only 2 event types on that page i have filtered the page based on these event Types and used following code to created the measure)
_IDTest = var _idTest = UNION( CALCULATETABLE(VALUES('Table'[Id]),FILTER (ALLSELECTED('Table'), 'Table'[EventType] = "a")),CALCULATETABLE(VALUES('Table'[cid]),FILTER(ALLSELECTED('Table'), 'Table'[EventType] = "a"))) return if(SELECTEDVALUE('Table'[Id]) in _idTest,MAX('Table'[Id]),BLANK()))i got the following result
Id EventType Date cid 1 a 4/8/2020 7:59:51 PM 2 2 b 4/8/2020 6:26:20 PM 3 a 4/8/2020 8:03:47 PM 4 4 b 4/8/2020 8:03:47 PM Now i want to show the time diff between related event Types
for e.g. Lets say event type b happened at 4/8/2020 6:26:20 PM and related event type a happened at 7:59:51 PM so here i want to display the time difference between these two events (id 1 and 2) and group them accordingly and the second group will be for ids 3 and 4 and the time difference.
I also want to create the aggregations on the resulted datetime difference like min , max ,avg time etc.
Will you please guide me on this?
Thank you very much.
- v-kelly-msft
Hi PBILover ,
First create a calculated column as below:
_Markrow = var _cid=CALCULATETABLE(VALUES('Table'[cid]),FILTER(ALL('Table'),'Table'[EventType]="a")) Return IF('Table'[Id] in _cid,'Table'[Id],BLANK())Then create a measure as below:
_Datediff = var anotherdate =IF(MAX('Table'[_Markrow])=BLANK(),BLANK(),CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[cid]=MAX('Table'[_Markrow])))) return DATEDIFF(MAX('Table'[Date]),anotherdate,SECOND)Finally you will see: (here I calculated the time difference in second)
Here is the modified .pbix file.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!