Can I define a custom column variable based on a text search slicer value?

Hello. I have the below custom column in a Power BI report that uses a dataverse table column 'Job' as a variable. I had a slicer in the visual based on the 'Job' column as well. I realized I needed to include rows in the visual if the job number appears in either the 'Job' column OR 2 additionals columns called 'From' and 'To'. In my dataverse table I have a formula column 'FromTo' that concatenates the text from all 3 columns into a text string, and I have changed the report's visual slider to a text search slicer based on the 'FromTo' column. The visual now shows the correct data rows, but the custom column ignores the rows where the Job appears in 'From' or 'To' but not 'Job'. I want to replace var CurrentJob = crf38_fieldactions[Job] with 'FromTo' CONTAINS the text in the text search slicer, but I don't know if that's possible or what the syntax would be. Greatly appreciate any guidance.

RT = var CurrentDate = crf38_fieldactions[ActionDate]

var CurrentJob = crf38_fieldactions[Job]

var CurrentFAID = crf38_fieldactions[FAID]

var CurrentItemType = crf38_fieldactions[ItemType]

var FilteredTable = FILTER(crf38_fieldactions, crf38_fieldactions[ActionDate]<=CurrentDate && crf38_fieldactions[ActionDate] >= MIN(crf38_fieldactions[ActionDate])

&& crf38_fieldactions[Job]=CurrentJob && crf38_fieldactions[ItemType] = CurrentItemType)

return

If(crf38_fieldactions[Barrier Feet]<>0 && NOT(ISBLANK(crf38_fieldactions[Barrier Feet])),

CALCULATE(SUM(crf38_fieldactions[Barrier Feet]), FilteredTable))

Need Help

Anonymous

2 years ago

Hi lsealy ,

I created a sample pbix file(see the attachment), please check if that is what you want. You can follow the steps below to get it:

1. Add index column in Power Query Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtM30TcyMDJR0lEy0vU0NtF1ArKCgdizpKICRRDBMjY1UPBWMDTSM1VwC1FwzEtWCE/MyQFKmFuY65kqxeqAjDU0RJjram6GaS5MENVcLy+gwQppJTAjdQ1NTAxgRhoRcCp2Iw0NQObBDLQ0o9A4dBeaAY2gqoFGlHoY3UBDCwoNxBrZRqa0MNXMCJSCYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ActionDate = _t, Job = _t, Action = _t, ItemType = _t, From = _t, To = _t, ICODE = _t, #"Barrier Feet" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ActionDate", type date}, {"Job", type text}, {"Action", type text}, {"ItemType", type text}, {"From", type text}, {"To", type text}, {"ICODE", type text}, {"Barrier Feet", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Job", "ItemType"}, {{"Index", each Table.AddIndexColumn(_, "Index",1,1), type table }}),
    #"Expanded Index" = Table.ExpandTableColumn(#"Grouped Rows", "Index", {"ActionDate", "Action", "From", "To", "ICODE", "Barrier Feet", "Index"}, {"ActionDate", "Action", "From", "To", "ICODE", "Barrier Feet", "Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Index",{{"Job", type text}, {"ItemType", type text}, {"Action", type text}, {"From", type text}, {"To", type text}, {"ICODE", type text}, {"Barrier Feet", type number}, {"Index", Int64.Type}, {"ActionDate", type date}})
in
    #"Changed Type1"

2. Create a measure as below

RT = 
VAR _index =
    SELECTEDVALUE ( crf38_fieldactions[Index] )
VAR _job =
    SELECTEDVALUE ( crf38_fieldactions[job] )
VAR _date =
    SELECTEDVALUE ( crf38_fieldactions[ActionDate] )
VAR FilteredTable =
    FILTER (
        ALLSELECTED ( crf38_fieldactions ),
        crf38_fieldactions[Job] = _job
            && crf38_fieldactions[Index] <= _index
            && crf38_fieldactions[ActionDate] <= _date
    )
RETURN
    IF (
        SELECTEDVALUE ( crf38_fieldactions[Barrier Feet] ) <> 0
            && NOT ( ISBLANK ( SELECTEDVALUE ( crf38_fieldactions[Barrier Feet] ) ) ),
        CALCULATE ( SUM ( crf38_fieldactions[Barrier Feet] ), FilteredTable )
    )

3. Create a table visual and add index field onto the table visual

Best Regards

Define a custom column variable based on a text search slicer value.pbix54 KB

4 Replies

rajendraongole1
Super User
2 years ago
Hi lsealy - Can you try with below measure instead of using a calculated column

Modified measure:

Measure_Result =
VAR CurrentDate = MAX(crf38_fieldactions[ActionDate])
VAR SelectedSearchText = SELECTEDVALUE(crf38_fieldactions[FromTo])
VAR FilteredTable =
FILTER(
ALL(crf38_fieldactions),
crf38_fieldactions[ActionDate] <= CurrentDate &&
crf38_fieldactions[ActionDate] >= MIN(crf38_fieldactions[ActionDate]) &&
SEARCH(SelectedSearchText, crf38_fieldactions[FromTo], 1, 0) > 0 &&
crf38_fieldactions[ItemType] = MAX(crf38_fieldactions[ItemType])
)
RETURN
IF(
MAX(crf38_fieldactions[Barrier Feet]) <> 0 && NOT(ISBLANK(MAX(crf38_fieldactions[Barrier Feet]))),
CALCULATE(SUM(crf38_fieldactions[Barrier Feet]), FilteredTable)
)

Correctly include rows where the job number appears in any of the Job, From, or To columns based on slicer selection.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
- lsealy
  Frequent Visitor
  2 years ago
  rajendraongole1 thank you so much. I think I need to use a calculated column instead of a measure, because I need the calculation to occur on each data row. I did paste your formula into a measure and received these results:
  - rajendraongole1
    Super User
    2 years ago
    Hi lsealy - Yes, please create a calculated column and test the values.
    By using the SELECTEDVALUE function to get the job number from the slicer and the SEARCH function to check for its presence in the FromTo column. check it and let know.
    I hope it works.
    
    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

Forum Discussion

Can I define a custom column variable based on a text search slicer value?

4 Replies

Related content

Defining Variables

un define

Defining a variable in a custom column?

User defined Custom Periods Slicer

Defining new customer