Finding column values based on two different Values of same table

[PBILover]

Hi,

I have below table in my dataset

Id	EventType	Date	cid
1	a	2020-05-01	2
2	b	2020-05-02
3	a	2020-05-02	4
4	b	2020-05-01
5	c	2020-05-03

step 1 :Apply filter on the table where eventType is a  , i will get following output

Id	EventType	Date	cid
1	a	2020-05-01	2
3	a	2020-05-02	4

step 2:From the above output  i want to  take cid (2,4) and id(1,3) and pass it to the original table, so the result will be like this

Id	EventType	Date	cid
1	a	2020-05-01	2
2	b	2020-05-02
3	a	2020-05-02	4
4	b	2020-05-01

Step 3 : want to find out the date diff between event Type a and b of the related id and cid

I have tried certain things like lookup function but it wont work.

Can you please help me inthis to resolve?

Thank you very much.

[v-kelly-msft]

Hi @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

Did I answer your question? Mark my post as a solution!

[PBILover]

@v-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,
Kelly

Did I answer your question? Mark my post as a solution!

[PBILover]

Hi @Kelly Thank you for your great help.

I am new to Dax and learning good things 🙂

Is it possible instead of displaying Table[id] we can display  table[cid] in the following code?

IF('Table'[Id] in _cid,'Table'[Id],BLANK())

I have tried but no luck 😞 

[v-kelly-msft]

Hi  @PBILover,

For question 1---->

Is it possible instead of displaying Table[id] we can display  table[cid] in the following code?

IF('Table'[Id] in _cid,'Table'[Id],BLANK())

No,you cant, this expression is to find the rows where the Id equals the cid list,so you can only get the id value back,but it doesnt matter,as the id values returned is exactly the cid values.

For question 2--------->

If you wanna use aggregation for the values,then create a calculated column as below:

Datediff = 
var anotherdate =IF('Table'[_Markrow]=BLANK(),BLANK(),CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[cid]=EARLIER('Table'[_Markrow])))) return
DATEDIFF('Table'[Date],anotherdate,SECOND)

And you will see:

Here is the modified .pbix file you can refer to .

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[PBILover · Accepted solution]

Yor are Awesome @Kelly!!
It Works!!

Now user Wants to display data as in below format

id	idDate	Cid	CidDate	Date Diff
1	4/08/2020 7:59:51 PM	2	4/08/2020 6:26:20 PM	5611
3	4/08/2020 8:03:47 PM	4	4/08/2020 8:03:47 PM	0

Will it be Possible?

I am exploring some options.

Thank you very much for your help.

[PBILover]

Hi @Kelly,

while calculating aggregation i get an error that we can not use measures in aggregate function. (I guess we can write a DAX for it , but might be a lengthy Code (need to work on DAX : )), so is it a good idea , Instaed of creating Measures , create another calculated column for finding the datedifference of the related ids and use this column for calculating aggregates(Min,Max,Avg etc)?

Appreciated your help.

Thank you,.

[parry2k]

@PBILover yes it can be done, you need a separate table with unique event type values, it will be purely used for slicing from there you can write a measure to find out all the ids

[PBILover]

@parry2k can you please little elaborate it more, sorry i am not that good in power BI yet.