Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |