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
Till
Resolver II
Resolver II

Flag for Values

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
Unbenannt.PNG

 

Thank´s for help 
Till

4 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

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

View solution in original post

Jihwan_Kim
Super User
Super User

Picture1.png

 

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 )
 
 
 
 

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


Go to My LinkedIn Page


View solution in original post

linh091
Resolver I
Resolver I

Hi Till,

 

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

0001.PNG

2. Pivoted only your 'Info' column

002.PNG

 3. Add Flag condition:

003.PNG

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

004.PNG

 

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"

View solution in original post

HarishKM
Impactful Individual
Impactful Individual

@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




View solution in original post

5 REPLIES 5
HarishKM
Impactful Individual
Impactful Individual

@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
Resolver I
Resolver I

Hi Till,

 

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

0001.PNG

2. Pivoted only your 'Info' column

002.PNG

 3. Add Flag condition:

003.PNG

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

004.PNG

 

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
Super User
Super User

Picture1.png

 

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 )
 
 
 
 

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


Go to My LinkedIn Page


amitchandak
Super User
Super User

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

Hi,

 

thank you very much fo the solutions,

 

Kind regards Till

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.