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

v-yingjl's avatar
v-yingjl
Community Support
6 years ago

Hi AvPowerBI ,

Based on your sample file, I have created a measure but get the different values like this after changing the cross filter direction from single to both

 

Measure =
VAR tab =
    ADDCOLUMNS (
        RELATEDTABLE ( Visits ),
        "flag", RELATED ( PersonCalendar[AvailableDayFlag] )
    )
RETURN
    COUNTROWS ( FILTER ( tab, [flag] = 1 ) )

 

 

After comparing table visits, there seems to be a lack of some logic to judge duplication in your table. 

 

Best Regards,
Yingjie Li

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

AvPowerBI's avatar
AvPowerBI
Post Patron
6 years ago

Hi Yingjie Li,

 

Thanks for your response to this, unfortunatley I was not getting the right results to  the DAX query you provided for me. With regards to "After comparing table visits, there seems to be a lack of some logic to judge duplication in your table. " it is not a Duplicate because if you see the column BrbKey there is a different number for the rows you have mentioned.

 

Thanks