Flag for Values

[Till]

Hi All,

I have the following challenge...
I need a column flag to represent the following.
If in a month the value for FTE is zero then a zero must be entered in the FLAG column for revenue and Fte. If in a month revenue and Fte values contain then a 1

Thank´s for help 
Till

[amitchandak · Accepted solution]

@Till , Try a new column like

new column =
var _1 = countx(filter(Table, [Date] =earlier([Date]) && [info] = "fte" && [value] =0),[Date])
return
if(_1 >0,0,1)

[Jihwan_Kim · Accepted solution]

Flag CC =

VAR _currentmonth =

MONTH ( Data[Date] )

VAR _filtertable =

FILTER ( Data, MONTH ( Data[Date] ) = _currentmonth && Data[info] = "fte" )

VAR _condition =

MAXX ( _filtertable, Data[value] ) = 0

RETURN

IF ( _condition = TRUE (), 0, 1 )

Link to the pbix file 

[linh091 · Accepted solution]

Hi Till,

You can do it in the Transform:
1. Enter your data

 

2. Pivoted only your 'Info' column

 3. Add Flag condition:

4. Unpivote Column if you want to back your expected format:

 

Here is the query code, you can try it.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyVNJRKkotS80rTQWyDA2AQClWB0kyrQQkARM0wtBhitBhhKJDz8gUKm6MrskIyRpjZE1GUEETfDpM0BwWCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Info = _t, Vaule = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Info", type text}, {"Vaule", type number}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Info]), "Info", "Vaule"),
#"Added Custom" = Table.AddColumn(#"Pivoted Column", "Flag", each if [fte] = 0 then 0 else 1),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Custom", {"Date", "Flag"}, "Attribute", "Value"),
#"Reordered Columns" = Table.ReorderColumns(#"Unpivoted Columns",{"Date", "Attribute", "Value", "Flag"})
in
#"Reordered Columns"

[HarishKM · Accepted solution]

@Till You can use this .
Go to power query and add a conditional col. give condition like this .
If (value >0,1,0)
Note : here i am doing if value is greater than 0 then give me 1, if not then give 0 for that .

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards,
Harish Mishra

[linh091 · Accepted solution]

Hi Till,

You can do it in the Transform:
1. Enter your data

 

2. Pivoted only your 'Info' column

 3. Add Flag condition:

4. Unpivote Column if you want to back your expected format:

 

Here is the query code, you can try it.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyVNJRKkotS80rTQWyDA2AQClWB0kyrQQkARM0wtBhitBhhKJDz8gUKm6MrskIyRpjZE1GUEETfDpM0BwWCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Info = _t, Vaule = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Info", type text}, {"Vaule", type number}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Info]), "Info", "Vaule"),
#"Added Custom" = Table.AddColumn(#"Pivoted Column", "Flag", each if [fte] = 0 then 0 else 1),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Custom", {"Date", "Flag"}, "Attribute", "Value"),
#"Reordered Columns" = Table.ReorderColumns(#"Unpivoted Columns",{"Date", "Attribute", "Value", "Flag"})
in
#"Reordered Columns"

[Jihwan_Kim · Accepted solution]

Flag CC =

VAR _currentmonth =

MONTH ( Data[Date] )

VAR _filtertable =

FILTER ( Data, MONTH ( Data[Date] ) = _currentmonth && Data[info] = "fte" )

VAR _condition =

MAXX ( _filtertable, Data[value] ) = 0

RETURN

IF ( _condition = TRUE (), 0, 1 )

Link to the pbix file 

[amitchandak · Accepted solution]

@Till , Try a new column like

new column =
var _1 = countx(filter(Table, [Date] =earlier([Date]) && [info] = "fte" && [value] =0),[Date])
return
if(_1 >0,0,1)

[Till]

Hi,

thank you very much fo the solutions,

Kind regards Till