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.AvailableFlag
,c.CustomerName
FROM
PersonCalendar ec
INNER JOIN
Visits v
ON
ec.Db = v.Db
and ec.PersonId = v.PersonId
and ec.PersonDay = Convert(Date,v.ArriveDttm)

INNER JOIN
Calls c
ON v.Db = c.Db
and v.CallNo = c.CallNo


Where

YEAR(v.ArriveDttm) in (2019,2020)
AND ec.Db = 'UK'
) a
Group By a.CustomerName
order by 1

 

Thanks

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

9 Replies

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

    Hi AvPowerBI ,

    I have tried but not certain:

    table =
VAR tab =
    CALCULATETABLE (
        VALUES ( ec[calendarkey] ),
        VALUES ( ec[availableflag] ),
        VALUES ( c[customername] ),
        FILTER (
            CROSSJOIN ( ec, b ),
            ec[db] = v[db]
                && ec[personid] = v[personid]
                && ec[personday] = FORMAT ( v[arrivedttm], "General Date" )
        ),
        FILTER ( CROSSJOIN ( v, c ), v[db] = c[db] && v[callno] = c[callno] ),
        OR ( YEAR ( v[arrivedttm] ) = 2019, YEAR ( v[arrivedttm] ) = 2020 )
            && ec[db] = 'UK'
    )
RETURN
    SUMMARIZE (
        tab,
        'c'[customername],
        "AvailableDays", SUM ( 'ec'[availableflag] )
    )

     

    Refer the following articles:

    1. from-sql-to-dax-projection 
    2. from-sql-to-dax-joining-tables/ 
    3. from-sql-to-dax-in-and-exists/ 
    4. from-sql-to-dax-grouping-data/ 
    5. from-sql-to-dax-filtering-data/ 

     

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