Aggregating data values

[rajaniesh]

HI,

I am new to PowerBI and I need some help. Here is my problem:

I have two tables

Claim table

Workitem ID  Hours Clocked

ABC                50

xyz                  100

Project Table

Project Name  Claim Code (Semicolon Separated)

Project 1           ABC;XYZ  Please note that It can be any number of claim Codes

Now I want to create a tabular report which depicts 

Project Name, Claim Code,  Actual Hours

Project1            ABC;XYZ     150

I was able to create measures and calculated columns by splitting the Woritems into rows but there are many rows coming and I only want to show one row for one project.

Any help would be greatly appreciated...

Regards
Rajaniesh

[v-chuncz-msft · Accepted solution]

@rajaniesh,

You may use DAX below to add a calculated column.

Column =
VAR d = ";"
RETURN
    SUMX (
        FILTER (
            Claim,
            SEARCH ( d & Claim[Workitem ID] & d, d & Project[Claim Code] & d, 1, 0 )
                > 0
        ),
        Claim[Hours Clocked]
    )

[Anonymous]

What you need is a Claim Code table that has 1 row per project.  You would then have a link between Claim Table and the Claim Code Table.  You then have a link between the Claim Code Table and the Project table.

Now when you create a Table or Matrix visual, you can put the Project as your first item and then the Hours Clocked as the second item.  From the aggregation dropdown, you could then select Sum.

[rajaniesh]

Thanks for the answer..Claim table is already containing the claim codes and project names along with the info regarding who claimed for against which project so I am wondering why do we need another table?

Regards
Rajaniesh

[Anonymous]

The purpose is to get a direct link from Project name down to the claims.  In the manner you have described, you can't do this.

You need to get a 1 to Many relationship between your hours and your claims.  Then you need a 1 to many relationship with your projects and your claim codes.

Your present data structure has 1 row for your project, but then your claim codes listed with a delimiter.  This would need to be split out (Power Query can do this).  If you did this as is, you would have many project rows trying to connect to many claim hours rows.  This isn't a good data structure, so the solution is to split up your current project table so it conforms to the 1 to many hierarchy.

[rajaniesh]

Thanks for your mail so If I understood it correctly I need to replicate these tables multiple times to make the relationship. I think I need to split the claim code table into two different tables first table shoing distinct claim codes and another one showing hours claimed against each claim code. SImilialy I need to split Project table so I can create one to many relationship with Project name to claim codes. Is that a correct understanding? I have another question: Shall I use same data source and recreate it multiple times by removing the columns ?

Regards
Rajaniesh