Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Help required - Power bi scenario

HI All,

 

I'm new to Power bi .....trying to design a measure as below

 

we have fact table Appointments which consists of 

Apointment IDAppointment start date Client NOTypeStatusFew othe rfields
123410/10/2018100ScreenComplete
123516/10/2018101screen Complete
123611/10/2018100testopen 
123717/10/2018102screenCancelled
      

 

Another table Which gives the result of appointment

 

Client NOresult
1011
1020

 

 

I'm trying to find out 

 

Appointment type screen with status completed and who have result as one --- which is easy

 

in the second step I need to find wether this client has a test apointment  in same table Appointment and this test appointment should be after screen apointemnt i.e test appointemnt start date > screen appointment date.

 

 

Many thanks in advance for your suggestions.

 

Need Help

2 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    hi, Anonymous

    After my test, You could use this formula as below

    Measure = var _table=CALCULATETABLE(SUMMARIZE(Table1,Table1[Client NO],"days", DATEDIFF(CALCULATE(MAX(Table1[Appointment start date ]),Table1[Type]="screen"),CALCULATE(MAX(Table1[Appointment start date ]),Table1[Type]="test"),DAY))) return
var _count= CALCULATE(COUNTAX(FILTER(_table,ISBLANK([days])=FALSE()),Table1[Client NO])) return
IF(ISBLANK(_count),0,_count)

    Result:

    here is pbix, please try it.

    https://www.dropbox.com/s/5hbnsdkvan3scad/Help%20required%20-%20Power%20bi%20scenario.pbix?dl=0

     

    Best Regards,

    Lin

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Many Thanks for the solution v-lili6-msft. I was just struggling hard trying many ways as I'm new to Powerbi/DAX.

       

      I still have the issue  when I filter with start date it picks up all the test appointments with in start date .

       

      I dont want to apply filter on test Appointment date .i.e

       

      IF I filter for a month .. I pick up all screens completed with result = 1 in the month and any subseqent test apointments which can be anytime in future i.e after screen date.

       

      Hope I dnt confused you much.

       

      Thanks again for this.