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-Joi...
Ashish_Mathur
Super User
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