Forum Discussion

FlowViz's avatar
FlowViz
Helper III
5 years ago
Solved

Help with creating a table from existing table

Hi guys,

 

I have a table in my dataset 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit' that looks like so:

 

 

I want to only pull in those rows for WorkItemID's that had a TagNames that contained the word 'Block' OR where the WorkItemID had a Blocked column that contained 'Yes' (as well as the times it was 'No' for that respective WorkItemID).

 

Previously when using it for just TagNames I had the following:

 

 

 

WorkItemsBlocked = 
FILTER (
    'WorkItems Blocked (since 1st Jan 20) - Do Not Edit',
    CALCULATE (
        COUNT ( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[TagNames] ),
        SEARCH ( "Block", 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[TagNames], 1, 0 ) > 0,
        ALLEXCEPT ( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit', 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[WorkItemId] )
    ) > 0
)

 

 

But I'm not sure how to account for both the TagNames ever containing blocked or the Blocked ever containing Yes.

 

Any advice? 

Need Help
  • richbenmintz's avatar
    richbenmintz
    5 years ago

    Hi FlowViz ,

     

    Thanks for the sample file, please see the calculated table formula below

    Sample Table = 
var filterTable = 
    CALCULATETABLE(
        VALUES( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[WorkItemId]), 
        FILTER('WorkItems Blocked (since 1st Jan 20) - Do Not Edit', 
        SEARCH("block", 
            'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[TagNames],1,0)>0 
            || lower( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[Blocked]) = "yes")
        )
return
CALCULATETABLE(
    'WorkItems Blocked (since 1st Jan 20) - Do Not Edit',  'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[WorkItemId] in filterTable, 
    lower( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[Blocked]) = "no" || lower( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[Blocked]) = "yes" 
    )

    creates the following table

    Thanks

  • richbenmintz's avatar
    richbenmintz
    5 years ago

    Hi FlowViz ,

    added an additional or condition

    WorkItemsBlocked2 = 
VAR filterTable =
    CALCULATETABLE (
        VALUES ( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[WorkItemId] ),
        FILTER (
            'WorkItems Blocked (since 1st Jan 20) - Do Not Edit',
            SEARCH (
                "Block",
                'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[TagNames],
                1,
                0
            ) > 0
                || LOWER ( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[Blocked] ) = "yes"
        )
    )
RETURN
    CALCULATETABLE (
        FILTER (
            'WorkItems Blocked (since 1st Jan 20) - Do Not Edit',
            'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[WorkItemId]
                IN filterTable
                    &&( (
                        SEARCH (
                            "Block",
                            'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[TagNames],
                            1,
                            0
                        ) > 0
                            || LOWER ( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[Blocked] ) = "yes"
                    )
                    || LOWER ( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[Blocked] ) = "no"
                    || LOWER ( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[Blocked] ) = "")
        )
    )

     

     

13 Replies

  • richbenmintz's avatar
    richbenmintz
    Resident Rockstar
    5 years ago

    Hi FlowViz ,

     

    Can you please provide the data as a table, will make things a lot easiser for the community to assist.

     

    Thanks

  • richbenmintz's avatar
    richbenmintz
    Resident Rockstar
    5 years ago

    Hi FlowViz ,

     

    I mocked up some some sample data, so this may not be perfect, but i believe the process is valid

    - create a variable with all the workitemids that contain blocked tags and are blocked equals yes

    - filter the table by the variable table where blocked = yes or no

     

    Sample Table = 
var filterTable = CALCULATETABLE(VALUES('Table'[WorkItemId]), FILTER('Table', SEARCH("blocked", 'Table'[TagName],1,0)>0 && lower('Table'[Blocked]) = "yes"))
return
CALCULATETABLE('Table', 'Table'[WorkItemId] in filterTable, lower('Table'[Blocked]) = "no" || lower('Table'[Blocked]) = "yes" )

     

     

    Hope this works for you

     

  • FlowViz's avatar
    FlowViz
    Helper III
    5 years ago

    For clarity, the new table should have any work item ID's that ever had a TagNames containing "block" and the associated history (each row is the history of changes to an item) OR any work item ID's that ever had a Blocked value of "Yes"and the associated history from the original table - WorkItems Blocked (since 1st Jan 20) - Do Not Edit

    • richbenmintz's avatar
      richbenmintz
      Resident Rockstar
      5 years ago

      Hi FlowViz ,

       

      Thanks for the sample file, please see the calculated table formula below

      Sample Table = 
var filterTable = 
    CALCULATETABLE(
        VALUES( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[WorkItemId]), 
        FILTER('WorkItems Blocked (since 1st Jan 20) - Do Not Edit', 
        SEARCH("block", 
            'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[TagNames],1,0)>0 
            || lower( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[Blocked]) = "yes")
        )
return
CALCULATETABLE(
    'WorkItems Blocked (since 1st Jan 20) - Do Not Edit',  'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[WorkItemId] in filterTable, 
    lower( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[Blocked]) = "no" || lower( 'WorkItems Blocked (since 1st Jan 20) - Do Not Edit'[Blocked]) = "yes" 
    )

      creates the following table

      Thanks