Forum Discussion

Emmy66's avatar
Emmy66
Helper V
5 years ago

Userelationship returns blank

Hi, Your assistance on this will be appreciated. Below is the SQL I am trying to replicate in DAX. SELECT Count(pg.ProgressId) FROM Progress pg INNER JOIN Project__ToProgress ptp ON ptp.progres...
Need Help
parry2k's avatar
parry2k
Super User
5 years ago

Emmy66 very hard to say, maybe throw some sample data in pbix and share thru one drive/google drive, although don't use FILTER to remove rows, it will have a  performance impact.

 

 

 

 

  • Emmy66's avatar
    Emmy66
    Helper V
    5 years ago

    Hi parry2k thanks for your reply. Please see link to pbix attached. The count returns 1730 as I stated earlier using the dax formula in earlier message but expected output is 1646 as per the SQL query

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      5 years ago

      Hi,

      Could you describe your question in simple English (instead of sharing an SQL statement), share a small dataset(s) and show the expected result.

      • Emmy66's avatar
        Emmy66
        Helper V
        5 years ago

        Hi Ashish_Mathur thanks for your reply. I'm trying to count the number of progress updates based on this formula. Pbix file shared in my reply above.

        ProgressUpdate =

            CALCULATE(

            COUNT(Progress[progressId]),

                FILTER(Progress,Progress[status]=1),

                    FILTER(Project,Project[status]=1

                    && Project[accepted]=1

                    && Project[archived]=0),

                        FILTER(Project_Sheet,Project_Sheet[status]=1

                        && Project_Sheet[os_archived]=0))

        result is 1730 but the expected output should be 1646. I suspect the reason is because of the inactive relationship between Project & Project_To_Progress. I applied the USERELATIONSHIP function to the above query but instead it retruned blank (USERELATIONSHIP(Project_ToProgress,Project). I hope my explanation helps. Thanks in advance for your assistance.