How to make a report: " counting of work'

[Anonymous]

Hi everyone !

I have two table as below:

Table one

Project	Tester(name)	Dev (name)
Project I	A	B
Project II	A,C	C
Project III	D	B,A

Table two

Name	email
A
B
C
D

I want to creat a report as below:

Name	Total join	Test	Dev
A	3 (--join 3 project I,II,III)	2	1
B	2		2
C	1	1	1
D	1	1	1

Please guide me ! I can't speak english not well.

Thank

[smpa01 · Accepted solution]

@Anonymous  bring your axis from table2 in the viz and use following measures

devCount = 
VAR _0 =
    ADDCOLUMNS (
        ADDCOLUMNS ( t1, "@newDev", SUBSTITUTE ( t1[Dev (name)], ",", "|" ) ),
        "@newTester", SUBSTITUTE ( t1[Tester(name)], ",", "|" )
    )
VAR _1 =
    GENERATE (
        _0,
        ADDCOLUMNS (
            GENERATESERIES ( 1, PATHLENGTH ( [@newDev] ) ),
            "@Dev", PATHITEM ( [@newDev], [Value], TEXT )
        )
    )
VAR _2 =
    ADDCOLUMNS (
        t2,
        "@Count", COUNTX ( FILTER ( _1, [@Dev] = EARLIER ( [Name] ) ), [@Dev] )
    )
RETURN
    SUMX ( _2, [@Count] )


testerCount = 
VAR _0 =
    ADDCOLUMNS (
        ADDCOLUMNS ( t1, "@newDev", SUBSTITUTE ( t1[Dev (name)], ",", "|" ) ),
        "@newTester", SUBSTITUTE ( t1[Tester(name)], ",", "|" )
    )
VAR _1 =
    GENERATE (
        _0,
        ADDCOLUMNS (
            GENERATESERIES ( 1, PATHLENGTH ( [@newTester] ) ),
            "@Tester", PATHITEM ( [@newTester], [Value], TEXT )
        )
    )
VAR _2 =
    ADDCOLUMNS (
        t2,
        "@Count", COUNTX ( FILTER ( _1, [@Tester] = EARLIER ( [Name] ) ), [@Tester] )
    )
RETURN
    SUMX ( _2, [@Count] )

finalCount = 
VAR _0 =
    ADDCOLUMNS (
        ADDCOLUMNS ( t1, "@newDev", SUBSTITUTE ( t1[Dev (name)], ",", "|" ) ),
        "@newTester", SUBSTITUTE ( t1[Tester(name)], ",", "|" )
    )
VAR _1 =
    GENERATE (
        _0,
        ADDCOLUMNS (
            GENERATESERIES ( 1, PATHLENGTH ( [@newDev] ) ),
            "@Dev", PATHITEM ( [@newDev], [Value], TEXT )
        )
    )
VAR _2 =
    ADDCOLUMNS (
        t2,
        "@Count", COUNTX ( FILTER ( _1, [@Dev] = EARLIER ( [Name] ) ), [@Dev] )
    )
VAR _11 =
    GENERATE (
        _0,
        ADDCOLUMNS (
            GENERATESERIES ( 1, PATHLENGTH ( [@newTester] ) ),
            "@Tester", PATHITEM ( [@newTester], [Value], TEXT )
        )
    )
VAR _12 =
    GROUPBY (
        SUMMARIZE ( UNION ( _1, _11 ), [@Dev], [Project] ),
        [@Dev],
        "@X", COUNTX ( CURRENTGROUP (), [Project] )
    )
VAR _13 =
    ADDCOLUMNS (
        t2,
        "@total", SUMX ( FILTER ( _12, [@Dev] = EARLIER ( t2[Name] ) ), [@X] )
    )
RETURN
    SUMX ( _13, [@total] )

pbix is attached here - https://1drv.ms/u/s!AkrysYUHaNRvhcV8WS7KYKFfAHwSdw?e=5KOyEs

[smpa01 · Accepted solution]

@Anonymous  bring your axis from table2 in the viz and use following measures

devCount = 
VAR _0 =
    ADDCOLUMNS (
        ADDCOLUMNS ( t1, "@newDev", SUBSTITUTE ( t1[Dev (name)], ",", "|" ) ),
        "@newTester", SUBSTITUTE ( t1[Tester(name)], ",", "|" )
    )
VAR _1 =
    GENERATE (
        _0,
        ADDCOLUMNS (
            GENERATESERIES ( 1, PATHLENGTH ( [@newDev] ) ),
            "@Dev", PATHITEM ( [@newDev], [Value], TEXT )
        )
    )
VAR _2 =
    ADDCOLUMNS (
        t2,
        "@Count", COUNTX ( FILTER ( _1, [@Dev] = EARLIER ( [Name] ) ), [@Dev] )
    )
RETURN
    SUMX ( _2, [@Count] )


testerCount = 
VAR _0 =
    ADDCOLUMNS (
        ADDCOLUMNS ( t1, "@newDev", SUBSTITUTE ( t1[Dev (name)], ",", "|" ) ),
        "@newTester", SUBSTITUTE ( t1[Tester(name)], ",", "|" )
    )
VAR _1 =
    GENERATE (
        _0,
        ADDCOLUMNS (
            GENERATESERIES ( 1, PATHLENGTH ( [@newTester] ) ),
            "@Tester", PATHITEM ( [@newTester], [Value], TEXT )
        )
    )
VAR _2 =
    ADDCOLUMNS (
        t2,
        "@Count", COUNTX ( FILTER ( _1, [@Tester] = EARLIER ( [Name] ) ), [@Tester] )
    )
RETURN
    SUMX ( _2, [@Count] )

finalCount = 
VAR _0 =
    ADDCOLUMNS (
        ADDCOLUMNS ( t1, "@newDev", SUBSTITUTE ( t1[Dev (name)], ",", "|" ) ),
        "@newTester", SUBSTITUTE ( t1[Tester(name)], ",", "|" )
    )
VAR _1 =
    GENERATE (
        _0,
        ADDCOLUMNS (
            GENERATESERIES ( 1, PATHLENGTH ( [@newDev] ) ),
            "@Dev", PATHITEM ( [@newDev], [Value], TEXT )
        )
    )
VAR _2 =
    ADDCOLUMNS (
        t2,
        "@Count", COUNTX ( FILTER ( _1, [@Dev] = EARLIER ( [Name] ) ), [@Dev] )
    )
VAR _11 =
    GENERATE (
        _0,
        ADDCOLUMNS (
            GENERATESERIES ( 1, PATHLENGTH ( [@newTester] ) ),
            "@Tester", PATHITEM ( [@newTester], [Value], TEXT )
        )
    )
VAR _12 =
    GROUPBY (
        SUMMARIZE ( UNION ( _1, _11 ), [@Dev], [Project] ),
        [@Dev],
        "@X", COUNTX ( CURRENTGROUP (), [Project] )
    )
VAR _13 =
    ADDCOLUMNS (
        t2,
        "@total", SUMX ( FILTER ( _12, [@Dev] = EARLIER ( t2[Name] ) ), [@X] )
    )
RETURN
    SUMX ( _13, [@total] )

pbix is attached here - https://1drv.ms/u/s!AkrysYUHaNRvhcV8WS7KYKFfAHwSdw?e=5KOyEs

[Anonymous]

thank you so much. Counting of test and dev are correct. But total is incorrect as I descrip. Total = project joined
Here, A join 3 project ( I, II,III), --> Total =3
B join 2 Project(I,III)--> Total=2
C join 1 Project (II) --> Total=1, C has 2 role in project II so Dev=1,test=1

[smpa01 · Accepted solution]

@Anonymous  answer revised

[AlexisOlson]

You can get the counts using CONTAINSSTRING. E.g.

TestCount =
VAR T2Name = SELECTEDVALUE ( Table2[Name] )
RETURN
    CALCULATE (
        COUNTROWS ( Table1 ),
        CONTAINSSTRING ( Table1[Tester], T2Name )
    )

DevCount =
VAR T2Name = SELECTEDVALUE ( Table2[Name] )
RETURN
    CALCULATE (
        COUNTROWS ( Table1 ),
        CONTAINSSTRING ( Table1[Dev], T2Name )
    )

Total =
VAR T2Name = SELECTEDVALUE ( Table2[Name] )
RETURN
    CALCULATE (
        COUNTROWS ( Table1 ),
        CONTAINSSTRING ( Table1[Tester], T2Name )
            || CONTAINSSTRING ( Table1[Dev], T2Name )
    )