Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello folks,
I have a table with 3 columns "Order", Account" and "Value" as following in Power Queries :
Order | Account | Value |
1 | A01 | 1 000,00 € |
2 | B01 | 1 500,00 € |
In the Matrix chart, I selected as "Order" Show all (1 & 2) and same for the "Account" (A01 & B01), so PBI displayed the entire table that I have in P Queries (It makes sense huh)
My issue is that I want to display all the possible intersections between Order and Account when there is no values linked to those rows. In other words, I need to display a table like this one:
Order | Account | Value |
1 | A01 | 1 000,00 € |
1 | B01 | |
2 | A01 | |
2 | B02 | 2 000,00 € |
Can you help ?
("The show items with no data" function in PBI Desktop doesn't work here)
Thanks
Solved! Go to Solution.
Hi @Anonymous ,
For your issue, do it like this:
My original data table:
//sheet1(3): the second original table; sheet1(4): the third
Table = CROSSJOIN('Sheet1 (3)', 'Sheet1 (4)')
//sheet1: the first original table
Column =
LOOKUPVALUE(
Sheet1[Value],
Sheet1[Order], 'Table'[Order],
Sheet1[Account], 'Table'[Account]
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
For your issue, do it like this:
My original data table:
//sheet1(3): the second original table; sheet1(4): the third
Table = CROSSJOIN('Sheet1 (3)', 'Sheet1 (4)')
//sheet1: the first original table
Column =
LOOKUPVALUE(
Sheet1[Value],
Sheet1[Order], 'Table'[Order],
Sheet1[Account], 'Table'[Account]
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |