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
johnphil
Regular Visitor

Add custom column with multiple criteria

Hi Everyone,

 

I am looking for ways to add a new column for the table using the criteria set below. Appreciate your help on this. Thank you.

 

Table

Table.JPG

 

Criteria

Criteria.JPG

1 ACCEPTED SOLUTION
PattemManohar
Community Champion
Community Champion

@johnphil You need to have bit transforming to your lookup table before you use that. This can be achieved in the "Power Query Editor" as below:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnTUjYpS0lEyVIrViVYyNtA1tgTyjMA8AwNdI0sdEwNdS5CYsVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [BuType = _t, Identifier = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"BuType", type text}, {"Identifier", Int64.Type}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"BuType", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "BuType"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"BuType", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "BuType", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"BuType.1", "BuType.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"BuType.1", type text}, {"BuType.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"BuType.1", "BuTypeLower"}, {"BuType.2", "BuTypeHigher"}})
in
    #"Renamed Columns"

Now it will look like this...

 

image.png

 

Then, you can use this lookup table (Which was transformed as above) in your main table as below

 

Column = CALCULATE(VALUES(Test30Lkp[Identifier]),FILTER(Test30Lkp,Test30CalcField[BUType]>=Test30Lkp[BuTypeLower] && Test30CalcField[BUType] <= Test30Lkp[BuTypeHigher]))

Here is the expected output...

 

 image.png





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




View solution in original post

10 REPLIES 10
Thim
Resolver V
Resolver V

Here is my take on it.

Dublicate the BY Ty column under Edit Queries

change the format to whole numbers (This will error all letters)

Change all errors to 0.

End Edit Queries.

 

Now type this formula.

Identifier = IF('Table'[BU Ty - Copy]=0;1;IF('Table'[BU Ty - Copy]<30;3;IF('Table'[BU Ty - Copy]>39;3;2)))

 

This should give you, the wanted result.

BU Ty.PNG

Anonymous
Not applicable

As far as I understand it. There's no easy way to do it. You'll need to actually create a table that Holds each BU Type. Example

 

Capture.jpg

 

 

Anonymous
Not applicable

You can create a calcuated column and try the following DAX

 

Identifier =
IF (
    SEARCH ( "3", 'Type'[BU Type],, 0 ) = 1,
    "2",
    IF ( ISERROR ( VALUE ( 'Type'[BU Type] ) ), "1", "3" )
)

2018-10-24_17-04-55.png

 

PattemManohar
Community Champion
Community Champion

@johnphil You need to have bit transforming to your lookup table before you use that. This can be achieved in the "Power Query Editor" as below:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnTUjYpS0lEyVIrViVYyNtA1tgTyjMA8AwNdI0sdEwNdS5CYsVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [BuType = _t, Identifier = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"BuType", type text}, {"Identifier", Int64.Type}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"BuType", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "BuType"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"BuType", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "BuType", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"BuType.1", "BuType.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"BuType.1", type text}, {"BuType.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"BuType.1", "BuTypeLower"}, {"BuType.2", "BuTypeHigher"}})
in
    #"Renamed Columns"

Now it will look like this...

 

image.png

 

Then, you can use this lookup table (Which was transformed as above) in your main table as below

 

Column = CALCULATE(VALUES(Test30Lkp[Identifier]),FILTER(Test30Lkp,Test30CalcField[BUType]>=Test30Lkp[BuTypeLower] && Test30CalcField[BUType] <= Test30Lkp[BuTypeHigher]))

Here is the expected output...

 

 image.png





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




@PattemManohar

 

Tried this one by doing the following:

 

=CALCULATE (VALUES(Query1[Identifier]),FILTER(Query1, 'BU Type'[BU Ty] >= Query1[BuTypeLower] && 'BU Type'[BU Ty] <= Query1[BuTypeHigher]))

 

But I got this error:

 

"Calculation error in measure 'BU Type'[BU Type Identifier]: A single value for column 'BU Ty' in table 'BU Type' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

 

Don't know if I did it the way I should have...

 

Query 1.JPG

@johnphil It was a calculated column based on lookup table. You are trying to create a measure. Also, please make sure you have transformed the lookup table as mentioned above otherwise your lookupvalue function will fail.





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




How do i do it when I am only in Power Query?

 

Sorry if noob question

@johnphil If you want to do this Power Query, then you need to write a custom LOOKUP function using M-Code.





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Anonymous
Not applicable

Hi @johnphil,

 

Can you elaborate more on what you need? Is the Criteria, values between AA and ZZ should have identifier 1, values between 30 and 39 should have identifier 2 etc.? And the new column you need is an identifier column?

Hi @Anonymous

 

"Is the Criteria, values between AA and ZZ should have identifier 1, values between 30 and 39 should have identifier 2 etc.? " Yes.

 

"And the new column you need is an identifier column?" Yes. This new column should have values like 1, 2 or 3.

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.