Forum Discussion
Report with three data sources - Filter on one value
Dear all,
I´m a bloody beginner with Power Bi and I struggle with a very simple challenge.
I have three data sources like this
Data1
Customer ID (for example 4711)
Quarter Name (for example 2016-Q1 or 2016-Q2)
Amount1
Data2
Customer ID (for example 4711)
Quarter Name (for example 2016-Q1 or 2016-Q2)
Amount2
Data3
Customer ID (for example 4711)
Quarter Name (for example 2016-Q1 or 2016-Q2)
Amount3
The report should look like this
Customer ID Quarter Name Amount1 AMount2 Amount3
It´s easy.
But how to filter now on Quarter Name = "2016-Q1"? I have three columns with quarter name and it´s not a 1:1 assignment
And how to compare "2016-Q1" with "2016-Q2" over three data sources.
Any hint is aprreciated.
Regards
Andy
Hi AndreasC,
I agree with Imke’s and I’d like to share some detail steps:
- Create tables.
Data1:
Data2:
Data3:
- Use lookupvalue() function to merge data to a new table.
Dax: Detail = SELECTCOLUMNS(Data1,"Customer",Data1[Customer ID],"Quarter",Data1[Quarter Name],"Amount1",Data1[Amount1],"Amount2",LOOKUPVALUE(Data2[Amount2],Data2[Customer ID],Data1[Customer ID],Data2[Quarter Name],Data1[Quarter Name]),"Amount3",LOOKUPVALUE(Data3[Amount3],Data3[Customer ID],Data1[Customer ID],Data3[Quarter Name],Data1[Quarter Name]))
In addition, if your data is not very neat, for example:
Data1:
1, 2015-Q1, 20,
1, 2015-Q3, 30,
2, 2016-Q2,50,
…
Data2:
1, 2015-Q2, 20,
1, 2016-Q3, 30,
2, 2016-Q2,50,
…
Data3:
1, 2015-Q2, 20,
1, 2016-Q4, 30,
2, 2016-Q2,50,
…
You can use ‘CROSSJOIN function’ to create a main table which use to merge other tables(CROSSJOIN(Customer table, CROSSJOIN(Year, Quarter)):
Dax: MainTable = SELECTCOLUMNS(
CROSSJOIN(UNION(ROW("Customer",1),ROW("Customer",2),ROW("Customer",3),ROW("Customer",4),ROW("Customer",5)),
CROSSJOIN(UNION(ROW("Year",2015),ROW("Year",2016)),UNION(ROW("Quarter","Q1"),ROW("Quarter","Q2"),ROW("Quarter","Q3"),ROW("Quarter","Q4")))),
"Customer",[Customer],
"Quarter Name", CONCATENATE([Year],"-"&[Quarter]))
Dax: Detail Records = SELECTCOLUMNS(MainTable,"Customer",MainTable[Customer],"Quarter",MainTable[Quarter Name],"Amount1",LOOKUPVALUE(Data1[Amount1],Data1[Customer ID],MainTable[Customer],Data1[Quarter Name],MainTable[Quarter Name]),"Amount2",LOOKUPVALUE(Data2[Amount2],Data2[Customer ID],MainTable[Customer],Data2[Quarter Name],MainTable[Quarter Name]),"Amount3",LOOKUPVALUE(Data3[Amount3],Data3[Customer ID],MainTable[Customer],Data3[Quarter Name],MainTable[Quarter Name]))
If you don't want to use a temp table, perhaps you could try to use below code, the dax formula seems very complex.
Table = SELECTCOLUMNS( SELECTCOLUMNS( CROSSJOIN(UNION(ROW("Customer",1),ROW("Customer",2),ROW("Customer",3),ROW("Customer",4),ROW("Customer",5)),CROSSJOIN(UNION(ROW("Year",2015),ROW("Year",2016)),UNION(ROW("Quarter","Q1"),ROW("Quarter","Q2"),ROW("Quarter","Q3"),ROW("Quarter","Q4")))),"Customer",[Customer],"Quarter Name",CONCATENATE([Year],"-"&[Quarter])),"Customer",[Customer],"Quarter",[Quarter Name],"Amount1",LOOKUPVALUE(Data1[Amount1],Data1[Customer ID],[Customer],Data1[Quarter Name],[Quarter Name]),"Amount2",LOOKUPVALUE(Data2[Amount2],Data2[Customer ID],[Customer],Data2[Quarter Name],[Quarter Name]),"Amount3",LOOKUPVALUE(Data3[Amount3],Data3[Customer ID],[Customer],Data3[Quarter Name],[Quarter Name]))
Regards,
Xiaoxin Sheng
4 Replies
Hi Andy,
What I think you want to do is merge your three data sources. This is done in the Query Editor in Power BI.
I'm assuming each data source is a unique query so you have three queries.
For each query, Add a Custom Column with the formula [Customer ID] & [Quarter Name]. That will concatenate the two columns to create a unique ID of CustomerID & Quarter you can use to merge the queries.
In your first query, click "Merge Queries." Select the second query from the drop-down menu and select your new ID fields in both tables.
You should now see a "NewColumn." Click the two arrows at the header and check only the "Amount2" column. You can also uncheck the "Use origninal column name as prefix."
Voila! You should now have the Amount2 column added to your first data set.
Repeat the merge for Amount3.
At the end, you can remove the Custom "ID" column you created so you don't see it in your data.
Very good recommendation in my eyes.
We can make it even just a little bit simpler, as we don't need to add a column to concatenate the [Customer ID] & [Quarter Name]: In the merge-step we can use multiple fields as keys to our joins. So just select the 2 key-fields in the same order (by holding the Strg-key) and you're done :-)
Hi AndreasC,
I agree with Imke’s and I’d like to share some detail steps:
- Create tables.
Data1:
Data2:
Data3:
- Use lookupvalue() function to merge data to a new table.
Dax: Detail = SELECTCOLUMNS(Data1,"Customer",Data1[Customer ID],"Quarter",Data1[Quarter Name],"Amount1",Data1[Amount1],"Amount2",LOOKUPVALUE(Data2[Amount2],Data2[Customer ID],Data1[Customer ID],Data2[Quarter Name],Data1[Quarter Name]),"Amount3",LOOKUPVALUE(Data3[Amount3],Data3[Customer ID],Data1[Customer ID],Data3[Quarter Name],Data1[Quarter Name]))
In addition, if your data is not very neat, for example:
Data1:
1, 2015-Q1, 20,
1, 2015-Q3, 30,
2, 2016-Q2,50,
…
Data2:
1, 2015-Q2, 20,
1, 2016-Q3, 30,
2, 2016-Q2,50,
…
Data3:
1, 2015-Q2, 20,
1, 2016-Q4, 30,
2, 2016-Q2,50,
…
You can use ‘CROSSJOIN function’ to create a main table which use to merge other tables(CROSSJOIN(Customer table, CROSSJOIN(Year, Quarter)):
Dax: MainTable = SELECTCOLUMNS(
CROSSJOIN(UNION(ROW("Customer",1),ROW("Customer",2),ROW("Customer",3),ROW("Customer",4),ROW("Customer",5)),
CROSSJOIN(UNION(ROW("Year",2015),ROW("Year",2016)),UNION(ROW("Quarter","Q1"),ROW("Quarter","Q2"),ROW("Quarter","Q3"),ROW("Quarter","Q4")))),
"Customer",[Customer],
"Quarter Name", CONCATENATE([Year],"-"&[Quarter]))
Dax: Detail Records = SELECTCOLUMNS(MainTable,"Customer",MainTable[Customer],"Quarter",MainTable[Quarter Name],"Amount1",LOOKUPVALUE(Data1[Amount1],Data1[Customer ID],MainTable[Customer],Data1[Quarter Name],MainTable[Quarter Name]),"Amount2",LOOKUPVALUE(Data2[Amount2],Data2[Customer ID],MainTable[Customer],Data2[Quarter Name],MainTable[Quarter Name]),"Amount3",LOOKUPVALUE(Data3[Amount3],Data3[Customer ID],MainTable[Customer],Data3[Quarter Name],MainTable[Quarter Name]))
If you don't want to use a temp table, perhaps you could try to use below code, the dax formula seems very complex.
Table = SELECTCOLUMNS( SELECTCOLUMNS( CROSSJOIN(UNION(ROW("Customer",1),ROW("Customer",2),ROW("Customer",3),ROW("Customer",4),ROW("Customer",5)),CROSSJOIN(UNION(ROW("Year",2015),ROW("Year",2016)),UNION(ROW("Quarter","Q1"),ROW("Quarter","Q2"),ROW("Quarter","Q3"),ROW("Quarter","Q4")))),"Customer",[Customer],"Quarter Name",CONCATENATE([Year],"-"&[Quarter])),"Customer",[Customer],"Quarter",[Quarter Name],"Amount1",LOOKUPVALUE(Data1[Amount1],Data1[Customer ID],[Customer],Data1[Quarter Name],[Quarter Name]),"Amount2",LOOKUPVALUE(Data2[Amount2],Data2[Customer ID],[Customer],Data2[Quarter Name],[Quarter Name]),"Amount3",LOOKUPVALUE(Data3[Amount3],Data3[Customer ID],[Customer],Data3[Quarter Name],[Quarter Name]))
Regards,
Xiaoxin Sheng
