Forum Discussion
Multiple values were supplied - Error
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] ) )
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.
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
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] ) )That's amazing, it works. Thank you very much!