Forum Discussion

kamran's avatar
kamran
Frequent Visitor
7 years ago

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...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
7 years ago

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

  • kamran's avatar
    kamran
    Frequent Visitor
    7 years ago

    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 IDCategoryQuantityComments
    1.111ABC
    1.213XYZ
    2.122JKL
    3.135 
    3.2311MNO
    1.114 
    3.136ABC
    2.125 
    1.218XYZ
    3.131ABC
    3.237PQR

     

     

    Result    
     Distinct Count of Comments 
    Categories / Product ID123Total
    1.11  1
    1.21  1
    2.1 1 1
    3.1  11
    3.2  22
    Total2136

     

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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