Userelationship returns blank

[Emmy66]

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.progressId = pg.progressId
INNER JOIN Project pj ON pj.uid = ptp.ProjectUid
INNER JOIN Project_sheet ps ON ps.uid = pj.project_sheet_id
WHERE ps.status = 1 AND pj.archived= 0 AND ps.os_archived=0
AND pg.status = 1
AND pj.accepted = 1 AND pj.status = 1
In my model, the relationship between Project and Project_ToProgress is inactive as shown in my schema below.

But when I include userelationship in the query, it returns blank but without it returns 1730 as against the SQL query which returns 1646.

ProgressUpdate =

    CALCULATE(

    COUNT(Progress[progressId]),

       USERELATIONSHIP(Project_ToProgress,Project),

        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))

I would appreciate some help on this as I do not know what I am doing wrong.

Thanks in advance.

[v-janeyg-msft]

Hi, @Emmy66 

After testing, the filter fuction you wrote seems to be redundant.You can delete it then will get the right value.

Like this:

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Emmy66]

hi @v-janeyg-msft , thanks for your reply. The conditions are required. What I attached is a sample extract based on the conditions included in the filter in the SQL inorder to reduce the volume of data. Each table has records of over 100K, so without the filters I will be doing just the count of records and that will not produce the required result. Apologies if my explanation wasn't clear earlier.

[v-janeyg-msft]

Hi, @Emmy66 

You have a large amount of data, and problems are difficult to find. I suggest you optimize the data model(A fact table and related dimension tables). Can the relationship be changed to a star structure? Maybe the problem will be corrected.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Emmy66]

Hi @v-janeyg-msft, thanks for responding to my query. We are planning on rolling out a datamart which I hope will alleviate the challenges I'm currently facing.

[parry2k]

@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]

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]

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]

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.