Forum Discussion

AvPowerBI's avatar
AvPowerBI
Post Patron
6 years ago
Solved

How to write similar DAX query as per Subquery in SQL Server

Hi,   How would I write a smilar DAX query based on the below T-SQL query:   SELECT a.CustomerName ,SUM(a.AvailableFlag) AS AvailableDays FROM ( SELECT DISTINCT ec.CalendarKey ,ec.Ava...
Need Help
  • HotChilli's avatar
    HotChilli
    6 years ago

    Repeating values like this are generally indicative of a relationship issue.

    Here the Visits table can't filter the PersonCalendar due to the direction of the relationship.

    Solutions : change the direction to 'bidirectional' between Visits and PersonCalendar . Will work but may cause relationship problems for other results

    OR

    rework the data model. This would depend upon what you are trying to report on

    OR

    use CROSSFILTER to filter the PersonCalendar for just this measure e.g.

    FiltAvailDays = CALCULATE([AvailableDays], CROSSFILTER(PersonCalendar[PersonIdDateKey], Visits[PersonIdDateKey], Both))

HotChilli's avatar
HotChilli
Community Champion
6 years ago

Repeating values like this are generally indicative of a relationship issue.

Here the Visits table can't filter the PersonCalendar due to the direction of the relationship.

Solutions : change the direction to 'bidirectional' between Visits and PersonCalendar . Will work but may cause relationship problems for other results

OR

rework the data model. This would depend upon what you are trying to report on

OR

use CROSSFILTER to filter the PersonCalendar for just this measure e.g.

FiltAvailDays = CALCULATE([AvailableDays], CROSSFILTER(PersonCalendar[PersonIdDateKey], Visits[PersonIdDateKey], Both))

AvPowerBI's avatar
AvPowerBI
Post Patron
6 years ago

Hi HotChilli, that seems to be working and getting the right results, I may need to raise another question in future as I haven't fully tested with other Dimensions to the Rows I will be incorporating to the same Matrix.

 

Thanks