Forum Discussion
Need help in applying filters and getting multiple column data with respect to ID
Hello All,
I am new Power BI and not getting how to apply filter in below condition.
kindly help me on this issue.
I am having Excel data like,
| Date | EmployeeID | SickLeaves | PaidLeaves | Holidays | UnpaidLeaves |
| 20190501 | 1993037 | Not Well | - | - | - |
| 20190502 | 1993037 | - | - | - | - |
| 20190503 | 1993037 | - | Saturday | - | - |
| 20190504 | 1993037 | - | Sunday | - | - |
| 20190505 | 1993037 | - | - | - | - |
| 20190506 | 1993037 | Not Well | - | - | - |
| 20190507 | 1993037 | - | - | - | - |
| 20190508 | 1993037 | - | - | Holiday | - |
| 20190509 | 1993037 | - | - | - | - |
| 20190510 | 1993037 | - | - | - | For Personal Reason |
| 20190501 | 2001234 | Not Well | - | - | - |
| 20190502 | 2001234 | Not Well | - | - | - |
| 20190503 | 2001234 | - | - | - | - |
| 20190504 | 2001234 | - | Saturday | - | - |
| 20190505 | 2001234 | - | Sunday | - | - |
| 20190506 | 2001234 | - | - | - | - |
| 20190507 | 2001234 | - | - | - | - |
| 20190508 | 2001234 | - | - | Holiday | - |
| 20190509 | 2001234 | - | - | - | - |
| 20190510 | 2001234 | - | - | - | - |
| 20190501 | 2345654 | - | - | - | For Personal Reason |
| 20190502 | 2345654 | - | - | - | - |
| 20190503 | 2345654 | - | - | - | - |
| 20190504 | 2345654 | - | Saturday | - | - |
| 20190505 | 2345654 | - | Sunday | - | - |
| 20190506 | 2345654 | Not Well | - | - | - |
| 20190507 | 2345654 | - | - | - | - |
| 20190508 | 2345654 | - | - | Holiday | - |
| 20190509 | 2345654 | - | - | - | - |
| 20190510 | 2345654 | - | - | - | - |
I am trying create a Calculated Column or Measure to achieve folowing result and i am trying to show in Table view like below,
From above Excel data i am trying to display Three columns in Power BI Table as below. But i am not getting how to apply filter so that it will display only Leave date of Employee with ID, Date and Reason. Like this my Excel contains many Employees data for many years.
| LeaveDate | EmployeeID | LeaveReason |
| 20190501 | 1993037 | Not Well |
| 20190503 | 1993037 | Saturday |
| 20190504 | 1993037 | Sunday |
| 20190506 | 1993037 | Not Well |
| 20190508 | 1993037 | Holiday |
| 20190510 | 1993037 | For Personal Reason |
| 20190501 | 2001234 | Not Well |
| 20190502 | 2001234 | Not Well |
| 20190504 | 2001234 | Saturday |
| 20190505 | 2001234 | Sunday |
| 20190508 | 2001234 | Holiday |
| 20190501 | 2345654 | For Personal Reason |
| 20190504 | 2345654 | Saturday |
| 20190505 | 2345654 | Sunday |
| 20190506 | 2345654 | Not Well |
| 20190508 | 2345654 | Holiday |
Kindly help me on this issue.
Thanks in advance!!
Regards,
Amar
Refer the new column in table in the attached file after signature
Column = SWITCH(TRUE(),Sheet1[SickLeaves]<>"-",[SickLeaves],[PaidLeaves]<>"-",[PaidLeaves],[Holidays]<>"-",[Holidays],[UnpaidLeaves]<>"-",[UnpaidLeaves],"No Leave")
6 Replies
- amitchandak
Super User
Anonymous
If they are blank the
coalesce(table[SickLeaves],table[PaidLeaves],table[Holidays],table[UnpaidLeaves])
Or try like
switch(true(),
table[SickLeaves]<>"-",table[SickLeaves],
table[PaidLeaves]<>"-",table[PaidLeaves],
table[Holidays]<>"-",table[Holidays],
table[UnpaidLeaves]<>"-",table[UnpaidLeaves],
"Other"
)Hello Amit,
Thank you so much for your quick response.
I tried creating Calculated column with Switch as above but it not showing as i wanted show. If Employee has not taken any Leave then in the List that Employee should not be listed.
And i am trying to display Date, EmployeeID and LeaveReason if there is Leave for that Employee then only it should display otherwise not. For all employees Saturday and Sunday and Holidays are compulsory to display. So what i am trying here is just filter Leaves column and if any leaves are there then display it Date, EmployeeID and Reason.
By using above switch solution it is displaying only others i.e. false condition for all Employees.
And instead of "-" Excel is having "Blanks".
And i tried with "COALESCE" and it showing reasons. But for this i have created Calculated Column and i have added all Leaves Columns in it.
And in Table i have dragged Date , EmployeeID and And this newly created CAlculated column. It shows all Employees Data with Blanks also. But i trying to show only Leaves Data only.
I am trying with applying filters but it is not working. Kindly help!
Regards,
Amar
- amitchandak
Refer the new column in table in the attached file after signature
Column = SWITCH(TRUE(),Sheet1[SickLeaves]<>"-",[SickLeaves],[PaidLeaves]<>"-",[PaidLeaves],[Holidays]<>"-",[Holidays],[UnpaidLeaves]<>"-",[UnpaidLeaves],"No Leave")
- Ashish_Mathur
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", Int64.Type}, {"EmployeeID", Int64.Type}, {"SickLeaves", type text}, {"PaidLeaves", type text}, {"Holidays", type text}, {"UnpaidLeaves", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","-",null,Replacer.ReplaceValue,{"SickLeaves", "PaidLeaves", "Holidays", "UnpaidLeaves"}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Date", "EmployeeID"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}) in #"Removed Columns"Hope this helps.
