Forum Discussion
Display exclusive values in a table
Hi,
I have 2 tables:
Bucket Master:
| Flavor Name | Bucket |
| P1 | Premium |
| P2 | Premium |
| P3 | Premium |
| K1 | KK |
| K2 | KK |
| K3 | KK |
| K4 | KK |
| K5 | KK |
| L1 | LC |
| L2 | LC |
| L3 | LC |
| PF1 | PFF |
| PF2 | PFF |
| PF3 | PFF |
| PF4 | PFF |
| U1 | UC |
| U2 | UC |
Flavor List:
| ID | Flavor Name | Favor Count |
| 1 | P1 | 2 |
| 1 | L1 | 3 |
| 2 | K1 | 2 |
| 2 | K5 | 1 |
| 2 | PF1 | 4 |
| 3 | U1 | 5 |
| 3 | PF4 | 3 |
| 4 | P1 | 1 |
| 4 | P2 | 2 |
| 4 | K3 | 3 |
I created a summary table using 'Bucket Master':
| Bucket | Total |
| Premium | 3 |
| KK | 5 |
| LC | 3 |
| PFF | 4 |
| UC | 2 |
This is the current scenario:
I want to have a 4th column in second table(right-most), which will tell me which flavors are not available in a selected ID:
| Bucket | Total | Flavors Present | Missing Flavors |
| KK | 5 | 2 | K2, K3, K4, K5 |
| PFF | 4 | 1 | PF2,PF3,PF4 |
Please let me know if there is any way to achieve this?
Here is my data model:
Hi,
try two these measures:Flavors Present =DISTINCTCOUNTNOBLANK('Flavor List'[Flavor Name])Missing Flavors =CONCATENATEX (FILTER ( 'Flavor Master' , NOT [Flavor Name] IN VALUES ( 'Flavor List'[Flavor Name] ) ),[Flavor Name],",")
3 Replies
Natallia_23Resolver I
Hi,
try two these measures:Flavors Present =DISTINCTCOUNTNOBLANK('Flavor List'[Flavor Name])Missing Flavors =CONCATENATEX (FILTER ( 'Flavor Master' , NOT [Flavor Name] IN VALUES ( 'Flavor List'[Flavor Name] ) ),[Flavor Name],",")Thank you so much Natallia_23 . It worked great.
Hi Natallia_23, there's a small problem:
Bucket Total Flavors Present Missing Flavors KK 3 2 K2, PF1,PF2,PF3,PF4 PFF 3 1 PF2,PF3,K1,K2,K3 If total column has same number of flavors, the context which power BI considers takes a hiccup and it'll mix and match for those 2 buckets.
Any help on how to handle this will be appreciated.