Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
srini
Frequent Visitor

how to display

Hi

 

I have a query.

 

I have two tables one has 

 

ID START_DT            NAME
1 04/10/2017 00:00 Test1
2 01/01/2017 00:00 Test2
3 06/05/2017 00:00 Test3
4 07/10/2017 00:00 Test4 


Another table like this 

 

ID START_DT            END_DATE
2 01/01/2017 00:00 06/30/2017 00:00:00
3 06/05/2017 00:00 07/30/2017 00:00:00
1 04/10/2017 00:00
4 07/10/2017 00:00

 

how do i get a visualization in power bi which shows

 

if someone selects week1 or week 20, i want to display who people have left (which have end date) and people who are still working who dont have a end date.

 

I would like to give the week as a parameter or slicer. I could do thsi in SQL as a report, but i was told they want

something visually to see.

 

Also can the week be changed (if ISO week or our Financial week which starts from April )

 

Can someone please guide me.

 

Thanks

 

Sree

8 REPLIES 8
v-caliao-msft
Employee
Employee

@srini,

 

I have tested it on my local environment, the steps below is for you reference.

  1. Create a date table.
    Date = ADDCOLUMNS(CALENDAR(DATE(2017,1,1),date(2017,7,31)),"WeekNumber","Week"&RIGHT("00"&WEEKNUM([Date]),2))
  2. Create a measure in Table 2.
    Measure2 =
    var maxdate = MAX('Date'[Date])
    var mindate = MIN('Date'[Date])
    var checkactivepeople = IF((MAX(Table2[START_DT])>=mindate&&MAX(Table2[START_DT])<=maxdate)||(MAX(Table2[END_DATE])>=mindate&&MAX(Table2[END_DATE])<=maxdate)||(ISBLANK(MAX(Table2[END_DATE]))),1,0)
    return checkactivepeople
  3. Use this measure in your filter.
    Capture.PNG

Results
Capture1.PNGCapture2.PNG

 

Regards,

Charlie Liao

 

Hi charlie

 

Morning

 

I am not sure if you have seen my earlier post but just a quick question on it

 

When we select a week, what i need is 

 

for people active the sql would be - where start_date is lessr than the weekday selectecd ex (05/07/2017) and end_date is null 

 

for people who have left - it would be start_date is less than (<)  weekday selected and end_Date is <= 05/07/2017

 

when i select the name of the person, its showing like this, is this right

pbi.png

 

 

 

 

 

Thanks

 

Sree

@srini,

 

for people active the sql would be - where start_date is lessr than the weekday selectecd ex (05/07/2017) and end_date is null  

for people who have left - it would be start_date is less than (<)  weekday selected and end_Date is <= 05/07/2017

 

When you selected week, there are 5 weekdays, which one do you need to used in you measure?

 

Regards,

Charlie Liao

 

 

 

 

 

Is there a better way of selecting a from date  and to date

 

I mean like between two dates, that would be ideal

 

Thanks

S

 

 

@srini,

 

I mean like between two dates, that would be ideal

 

If that is the case, then my original expression would work.

 

Regards,

Charlie Liao

I did try your solution, i wanted the names of the person as well and when i select the field to be displayed in the table, i am not sure ifs showing the results correctly as its duplicating if you see the image below

 


pbi.png

srini
Frequent Visitor

Hi Charlie

 

Can you please let me know on the above

 

Thanks

 

S

Hi Liao

 

Thanks very much responding to me.

 

 

One more question, when i include the name in the Active People Visual, I get people who have not left also.

 

Ideally I would like the name of the person as well. I just included the name but it doesnt look right.

 

Thanks

 

Sri

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.