Userelationship returns blank

Question

Hi,Your assistance on this will be appreciated.Below is the SQL I am trying to replicate in DAX.SELECTCount(pg.ProgressId)FROM Progress pgINNER JOIN Project__ToProgress ptp ON ptp.progressId = pg.progressIdINNER JOIN Project pj ON pj.uid = ptp.ProjectUidINNER JOIN Project_sheet ps ON ps.uid = pj.project_sheet_idWHERE ps.status = 1 AND pj.archived= 0 AND ps.os_archived=0AND pg.status = 1AND pj.accepted = 1 AND pj.status = 1In my model, the relationship between Project and Project_ToProgress is inactive as shown in my schema below.&nbsp;&nbsp;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 =&nbsp;&nbsp;&nbsp; CALCULATE(&nbsp;&nbsp;&nbsp; COUNT(Progress[progressId]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; USERELATIONSHIP(Project_ToProgress,Project),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER(Progress,Progress[status]=1),&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER(Project,Project[status]=1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &amp;&amp; Project[accepted]=1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &amp;&amp; Project[archived]=0),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER(Project_Sheet,Project_Sheet[status]=1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &amp;&amp; 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.

emmy66 · Answer

Hi&nbsp;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.&nbsp;

parry2k · Answer

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

emmy66 · Answer

Hi&nbsp;parry2k&nbsp;thanks for your reply. Please see link to&nbsp;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 · Answer

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 · Answer

Hi&nbsp;Ashish_Mathur&nbsp;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 =&nbsp;&nbsp;&nbsp; CALCULATE(&nbsp;&nbsp;&nbsp; COUNT(Progress[progressId]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER(Progress,Progress[status]=1),&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER(Project,Project[status]=1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &amp;&amp; Project[accepted]=1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &amp;&amp; Project[archived]=0),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER(Project_Sheet,Project_Sheet[status]=1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &amp;&amp; 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 &amp; 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.

Forum Discussion

Userelationship returns blank

8 Replies

Related content

TREATAS returns blanks but USERELATIONSHIP returns values

Measure is returning BLANK

TotalYTD returning blank

IF ISBLANK Return Blank

Selectedvalue returning blank