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.
Hi Experts, Can someone help me with the below please?
My requirement is show data in 2 tables - list of products a customer has and missing products from another table. Tried couple of solutions but I’m not getting the desired results.
Table 1 – this table has 163,041 records and it is a list of all customer and their products
ID | Name | Product id | Product Name |
11 | A | 2 | B |
22 | B | 5 | E |
33 | C | 6 | F |
11 | A | 1 | A |
11 | A | 4 | D |
Table 2 – Product catalogue (around 2200 records)
Product ID | Product Name |
1 | A |
2 | B |
3 | C |
4 | D |
5 | E |
6 | F |
7 | G |
Desire results in PBI – On clicking customer name, i.e,., if I click on A, the corresponding tables below should show list of products that a customer has and missing products
ID | Name | Region |
11 | A | X |
22 | B | Y |
33 | C | z |
Customer products
Product Id | Product Name |
1 | A |
2 | B |
4 | D |
Missing products
Product ID | Product Name |
3 | C |
5 | E |
6 | F |
7 | G |
Solved! Go to Solution.
Hi @Anonymous,
We can use the feature that the table will hide blank value, create a measure using following
pname = VAR a = CALCULATETABLE ( VALUES ( Table1[Product Name] ), FILTER ( Table1, Table1[Name] = SELECTEDVALUE ( 'Table1'[Name] ) ) ) RETURN IF ( MAX ( Table2[Product Name] ) IN a, BLANK (), 1 )
Then we can use this feature to meet your second requirement. But remember to close the “show items with no data” option.
BTW, pbix as attached.
Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more
Hi @Anonymous,
We can use the feature that the table will hide blank value, create a measure using following
pname = VAR a = CALCULATETABLE ( VALUES ( Table1[Product Name] ), FILTER ( Table1, Table1[Name] = SELECTEDVALUE ( 'Table1'[Name] ) ) ) RETURN IF ( MAX ( Table2[Product Name] ) IN a, BLANK (), 1 )
Then we can use this feature to meet your second requirement. But remember to close the “show items with no data” option.
BTW, pbix as attached.
Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more
Hi @Anonymous ,
What have you tried so far?
The first requirement works with no relationship between the two tables.
Nathaniel
Proud to be a Super User!
Hi Nathaniel, I'm struggling with the missing product table tried couple of solutions that I found in this forum but i'm not getting the correct results, i.e., the missing product table is showing all products.
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 |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |