cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PBILover
Helper IV
Helper IV

Finding column values based on two different Values of same table

Hi,

I have below table in my dataset

 

IdEventTypeDatecid
1a2020-05-01 2
2b2020-05-02 
3a2020-05-024
4b2020-05-01  
5c2020-05-03  

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

IdEventTypeDatecid
1a2020-05-01 2
3a2020-05-024

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

 

IdEventTypeDatecid
1a2020-05-01 2
2b2020-05-02 
3a2020-05-024
4b2020-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.

1 ACCEPTED SOLUTION

Yor are Awesome @Kelly!!
It Works!!

Now user Wants to display data as in below format

 

ididDateCidCidDateDate Diff
14/08/2020 7:59:51 PM24/08/2020 6:26:20 PM5611
34/08/2020 8:03:47 PM44/08/2020 8:03:47 PM0


Will it be Possible?

I am exploring some options.

Thank you very much for your help.

View solution in original post

9 REPLIES 9
v-kelly-msft
Community Support
Community Support

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:

Annotation 2020-05-22 111208.png

For the related .pbix file.pls click here.

 

 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

@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 

 

IdEventTypeDatecid
1a4/8/2020 7:59:51 PM  2
2b 4/8/2020 6:26:20 PM 
3a4/8/2020 8:03:47 PM4
4b4/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.


 

 

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)

Annotation 2020-05-27 085740.png

Here is the modified .pbix file.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

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 😞 

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:

Annotation 2020-05-28 084627.png

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

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

 

Yor are Awesome @Kelly!!
It Works!!

Now user Wants to display data as in below format

 

ididDateCidCidDateDate Diff
14/08/2020 7:59:51 PM24/08/2020 6:26:20 PM5611
34/08/2020 8:03:47 PM44/08/2020 8:03:47 PM0


Will it be Possible?

I am exploring some options.

Thank you very much for your help.


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
Super User
Super User

@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

 

 

 






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors