Help with creating a table from existing table

[FlowViz]

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? 

[richbenmintz · Accepted solution]

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

[FlowViz]

Amazing! Thank you, this looks to do the trick!

[FlowViz]

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 · Accepted solution]

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

[FlowViz]

Thank you so much!

[richbenmintz]

happy to help

[FlowViz]

I just did another check with another dataset where they only use the tag of "blocked" and do not use blocked "yes" and I get a blank table which is not correct. 

I've attached this link to the file/dataset I mention, this should have data as they have items where 'blocked' is used in TagNames...

[richbenmintz]

Hi @FlowViz ,

Sorry for the Delay, here is the updated Table Query, changed the logic in the returned table filter to included ('blocked' or 'yes') or 'no' rows 

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 (
                "Blocked",
                '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 (
                            "Blocked",
                            '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"
        )
    )

Hope this helps,

[FlowViz]

So that's getting there. It still seems to be missing rows for items that at one point had the 'blocked' TagNames.

Here's a link to the old way/image below of how I'd like it to be:

Here's a link to the new way/image of how it currently looks:

So those 2 occasions where TagNames was blank/not containing Blocked need to be there. Can that be done?

[richbenmintz · Accepted solution]

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] ) = "")
        )
    )

[FlowViz]

One further question - what about an instance where there is no 'Blocked' column in the source table? I've found instances where this can be the case...could the calculated table account for this?

[FlowViz]

Sure thing, .pbix file here

[richbenmintz]

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

[richbenmintz]

Hi @FlowViz ,

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

Thanks