Need help in applying filters and getting multiple column data with respect to ID

[Anonymous]

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

[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.

[amitchandak]

@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"
)

[Anonymous]

@amitchandak 

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 · Accepted solution]

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")

[Anonymous]

@amitchandak 

Hello Amit,

Thanks for your quick response.

It is displaying all Leaves data Correctly. 

What i did here is i have created Calculated column with above DAX. 

I created Table and i inserted Date EmployeeID and Above CAlculated column. And it shows like below,

Here i dont want to display other Employees data if they dont have any Leaves. I tried with below DAX but it is showing true or false of 1 or zero.

IF(not ISBLANK('デイリーデータsample'[NGLeavesData]),1)

i am trying to display only DAte , EmployeeID and Reason. And if remove 1 from IF then gives error.

Kindly help on this .

Thanks in advance!

Regards,

Amar

[V-pazhen-msft]

@Anonymous 

You already have blanks instead of "-" in excel, I guess merge in query editor is a much easier way.

1. Merge the 4 columns

2. Close and Apply, put the columns in a table visual then filter the merged column.

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly