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

AvPowerBI's avatar
AvPowerBI
Post Patron
6 years ago

Hi V-yingl,

 

Thanks for your help so far, I've tried the way you have mentioned but was having issues implenting it and didn't realise it would be that complex the DAx query to get the result I wanted.

I have therefore provided an example and added the spreadsheet and pbix file on One Drive, you will see the results of what I want from the oneshare link below

 

https://1drv.ms/u/s!Aknl2UdxdHn0cTzGDcZPqkEDXcI?e=KXvDVU 

 

 

 

Wish to have the below Results, when the Dimension is CustomerName but it keeps repeating the same value of 6

 

 

Thanks

 

AvPowerBI's avatar
AvPowerBI
Post Patron
6 years ago

Wish to have the below Results, when the Dimension is CustomerName but it keeps repeating the same value of 6

 

  • 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

      Thanks HotChilli,

      I will have a look at these DAX functions you have mentoned, just wanted to check was you able to access the OneDrive folder I mentioned on the above post?

       

      I am currently in a transition from QlikView to Power BI, where Qlikview had a function called Cyclic which you could cycle though different Dimensions and it would automatically work out the value for you, not having the same luck in Power BI I'm afraid.

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