CODEWITHSUNDEEP
powerbidaxdata-analysispowerbi-desktopmeasure
Sweepster
Sweepster

Reputation: 1949

Power BI DAX Not getting correct results when counting rows with FILTER and USERELATIONSHIP

I have a simple data set with a Calendar table and an Interviews table. The interviews table has the following columns: CreatedOn, ClosedOn, Region, Division, Language and Index.

The relationship between the two tables is established on the Interviews[CreatedOn] and Calendar[Date] column. An inactive relationship is established on the Interviews[ClosedOn] and Calendar[Date] columns.

I have a date slicer using Calendar[Date] to let the user select a date range (In this case, I am testing a date range of Jan 1 2024 to Jan 6 2024) and a page filter to exclude Division = 1.

I want a measure that will count the number of interviews closed whose ClosedOn date fits within the selected date range.

This measure works and returns the correct result of 2641:

Total Closed = CALCULATE(COUNTROWS(Interviews), USERELATIONSHIP(Interviews[ClosedOn], 'Calendar'[Date]))

I also want a measure that will count the number of interviews closed whose language was French and this is where I have a problem:

Total French Closed = CALCULATE(COUNTROWS(Interviews), FILTER(Interviews, Interviews[Language] = "French"))

The result is much too low. It is returning 1565 interviews but the expected result should be 2210.

If I do this. the result is blank:

Total French Closed = CALCULATE(COUNTROWS(Interviews), FILTER(Interviews, Interviews[Language] = "French"), USERELATIONSHIP(Interviews[ClosedOn], 'Calendar'[Date]))

What am I doing wrong?

A sample dataset can be found here.

Upvotes: 1

Views: 258

Answers (1)

davidebacci
davidebacci

Reputation: 30289

Total French Closed = CALCULATE(COUNTROWS(Interviews), USERELATIONSHIP('Calendar'[Date], Interviews[ClosedOn]),  Interviews[Language] = "French")

enter image description here

OR

Total French Closed = CALCULATE(COUNTROWS(Interviews), USERELATIONSHIP('Calendar'[Date], Interviews[ClosedOn]), FILTER(ALLSELECTED(Interviews[Language]), Interviews[Language] = "French"))

OR

Total French Closed = CALCULATE( CALCULATE(COUNTROWS(Interviews),  FILTER(Interviews, Interviews[Language] = "French")), USERELATIONSHIP('Calendar'[Date], Interviews[ClosedOn]))

As for the reason why. Sergio comes to the rescue:

https://www.sqlbi.com/articles/using-userelationship-in-dax/ enter image description here

Upvotes: 2

Related Questions