Forum Discussion

PBILover's avatar
PBILover
Helper V
6 years ago
Solved

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.

Need Help
  • PBILover's avatar
    PBILover
    6 years ago

    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.

9 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    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's avatar
      PBILover
      Helper V
      6 years ago

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

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    6 years ago

    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's avatar
      PBILover
      Helper V
      6 years ago

      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.


       

       

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        6 years ago

        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!