Forum Discussion
How to get results based on Left Join logic
Hi all
I've tried to produce results in a Matrix visualization using all possible ways I know about DAX, i.e RELATED, CROSSFILTER, NATURALLEFTOUTERJOIN and FILTER but unable to produce a Left-Joined result set.
My tables are joined with each other with Many-to-One relationship with each other based on common column names.
Though apparently looks quite a simple piece of work but I don't know what's stopping it.
When I'm applying the RELATED, CROSSFILTER etc, though it shows the Sold Quantitiy correctly but it ignores the Products not sold.
And when I omit the relationship functions, it displays all the products but displays Total Sales figure.
Can you please give me a solution for this.
My simplified form of data and wish list is as below:
| LookupProductCategories | |
| Product Category ID | Category Name |
| 1 | LAPTOP |
| 2 | Tablet |
| 3 | Cell Phone |
| LookupProduct | ||
| Product ID | Product Category ID | Product Name |
| 1.1 | 1 | DEL LAPTOP |
| 1.2 | 1 | HP LAPTOP |
| 1.3 | 1 | Microsoft LAPTOP |
| 2.1 | 2 | iPad |
| 2.2 | 2 | Samsung S3 |
| 3.1 | 3 | Nokia Phone |
| 3.2 | 3 | Samsung Phone |
| 3.3 | 3 | Huawei Phone |
| 3.4 | 3 | iPhone |
| Sales | |
| Product ID | Quantity |
| 1.1 | 1 |
| 1.2 | 3 |
| 2.1 | 2 |
| 3.1 | 5 |
| 3.2 | 11 |
| Result Set Needed | |
| Product ID | Quantity Sold |
| 1.1 | 1 |
| 1.2 | 3 |
| 1.3 | 0 |
| 2.1 | 2 |
| 2.2 | 0 |
| 3.1 | 5 |
| 3.2 | 11 |
| 3.3 | 0 |
| 3.4 | 0 |
3 Replies
- Ashish_Mathur
Super User
Hi Ashish
Thanks for the help, you'r almost there, apologies that I missed to add a key thing that is:
please ignore the SUM of Quanitity and try to do: [DistinctCount of Comments].
I need Categories in the Matrix columns, Product in the Rows, and their respective [DistinctCount of Comments], as below:
It's not letting me to do Count of Comments over Category & Product ID Where Comments Is Not Null ( <> "" OR NOT(ISBLANK() ).
Sales Product ID Category Quantity Comments 1.1 1 1 ABC 1.2 1 3 XYZ 2.1 2 2 JKL 3.1 3 5 3.2 3 11 MNO 1.1 1 4 3.1 3 6 ABC 2.1 2 5 1.2 1 8 XYZ 3.1 3 1 ABC 3.2 3 7 PQR Result Distinct Count of Comments Categories / Product ID 1 2 3 Total 1.1 1 1 1.2 1 1 2.1 1 1 3.1 1 1 3.2 2 2 Total 2 1 3 6 Alternate SQL could be like:
Select Cat.[Category ID], Prd.[Product ID], Count(distinct Sal.[Comments]) TotalComments From dbo.Category Cat Inner Join dbo.Product Prd On Prd.[Category ID] = Cat.[Category ID] Left Join dbo.Sales Sal On Sal.[Product ID] = Prd.[Product ID] Group By Cat.[Category ID], Prd.[Product ID]
Thanks
HI kamran,
You can try to use following measure to calculate non blank distinct comment count:
NONBlank Count = COUNTA(Sales[Comments])-COUNTBLANK(Sales[Comments])
Relationship map:
Regards,
Xiaoxin Sheng
