Multiple values were supplied - Error

[harrinho]

Trying to sort out something very weird. I have a table called CDL_SVCS_Proj_Practice_Prod_Split which contains list of projects with more than one row for each projects. In order to remove the duplicates and bring in only the distinct values along with another column from the same table, I create a new table called test through the following query

However, the column Project_ID return some blank rows which they don't exist in the original table where they are brought from. Table CDL_SVCS_Proj_Practice_Prod_Split contains no blank project IDs, but as per below the test table brings in some blank projects with a populated Viz Top BU, which donesn't make sense. In the original table there could not and there are not any populated Viz Top BU against a blank project Id. 

How could I create a table that would bring the distinct Project IDs from the original table along with the populated Viz Top BU? 

[v-xjiin-msft]

@harrinho

In your scenario, why are you using ROLLUPGROUP() function? If you want to select distinct Project_ID or make Project_ID as groups. You just need to use expression like:

test = SUMMARIZE(CDL_SVCS_Proj_Practice_Prod_Split,CDL_SVCS_Proj_Practice_Prod_Split[Project ID],…) as the second parameter for SUMMARIZE() function is the <groupBy_columnName>.

By the way, did you mean that in your source table one Project_ID got multiple Viz Top BU? So when you want to bring the distinct Project_IDs, you need to combine the Viz Top BU? Something like:

test = SUMMARIZE(CDL_SVCS_Proj_Practice_Prod_Split,
                CDL_SVCS_Proj_Practice_Prod_Split[ProductId],
                "Viz Top BU",CONCATENATEX(CDL_SVCS_Proj_Practice_Prod_Split,CDL_SVCS_Proj_Practice_Prod_Split[Viz Top BU],","))

Please share us some sample data from your source table and your desired result. It will help us understand your requirement more clearly.

Thanks,
Xi Jin. 

[harrinho]

Thank you very much for following up @v-xjiin-msft. So, what I am trying to do is a simple lookup which I don't understand why it doesn't work and retunrs the multiple values error which is so annoying in power bi. Tableau, domo and other tools would handle this so easily and seemlessly..Anyway. 

To answer your last question, no, one distinct Project Id will have only one Viz Top BU. But there will be more than rows with the same Project Id with the same Viz Top BU. For example: 

So basically, let share my goal on this. As you see, for the same project there might be different products. However, through a series of additions I was able to crete the column Viz Top BU which will be populated based on some criteria and it is now working fine.

All I want is to bring this Viz Top BU column in another table with distinct Project IDs (obvioysly they are similar).

The first table is called:  CDL_SVCS_Proj_Practice_Prod_Split - let's call it "table 1"

Table 1 is like that:

The second table on which I want to do a lookup and bring the Viz Top BU from the table 1 is called: CDL_Project_Details - let's call it "table 2"

Table 2 is like that: 

And this is the formula I use to add Column Viz Top BU in table 2 (with the error)

So in my understanding, the error occurs because table 1 has multiple rows with the same Project ID. Table 2 includes only distinct IDs and I tried to to the lookup in an Excel and despite the multiple rows of the same project in Table 1, the vlookup worked becaues the multiple rows of Project IDs have the SAME Viz Top BU assigned to them. It would indeed be a problem, if the same project had multiple Viz Top BU, but this is not an issue as the same Project have the same Viz Top BU. 

So, in order to overcome the multiple rows of the same project I came up with an idea of creating a dummy table (test), where I will bring in the Distinct Project ID from TABLE 1 along with their Viz Top BU. I tried to do that using the formula:

test = SUMMARIZE(CDL_SVCS_Proj_Practice_Prod_Split,ROLLUPGROUP(CDL_SVCS_Proj_Practice_Prod_Split[Project ID]),CDL_SVCS_Proj_Practice_Prod_Split[Viz Top BU])

So, the question is: How can I bring the Viz Top BU from Table 1 to Table 2 so as to populate it for every distinct Project ID? Or if this is not possible, how can I bring in a Test table the distinct Project IDs and the Viz Top BU from Table 1 and then try to do a Lookup between the the Test and Table 2?

Sorry for the long post, I tried to be as descriptive as possible. Let me know if you have any questions, unfortunately I can only share screenshots of the data set  

[Zubair_Muhammad · Accepted solution]

@harrinho

Try this

Viz_Top_BU =
CALCULATE (
    FIRSTNONBLANK ( CDL_SVCS_Proj_Practice_Prod_Split[Viz Top BU], 1 ),
    FILTER (
        CDL_SVCS_Proj_Practice_Prod_Split,
        CDL_SVCS_Proj_Practice_Prod_Split[Project ID] = CDL_Project_Details[Project ID]
    )
)

[harrinho]

That's amazing, it works. Thank you very much!