SQL to DAX Conversion

[Anonymous]

Hi everyone,

I have a working SQL query that I would like to put into PowerBI.  I'm not sure exaclty how to convert SQL to DAX so any help would greatly be appreciated thanks!

SELECT Left(AcademicSemester,3),
AcademicWeek, SUM(Swipe)/COUNT(Distinct AcademicSemester)
as AverageSwipe
FROM
Table
Where AcademicWeek is not NULL
Group BY AcademicWeek, Left(AcademicSemester,3)

[amitchandak · Accepted solution]

@Anonymous ,

You need have a new colum
AcademicSemester new= Left(Table[AcademicSemester],3)

Then put these two on visual AcademicSemester new , Table[AcademicWeek ] as unsummarize
and use this mesure
measure = divide(SUM(Table[Swipe]),Distinctcount(Table[AcademicSemester]))

[Anonymous]

@amitchandak 

Worked perfectly.  Thank you!

[Pragati11]

Hi @Anonymous ,

Please provide some sample data that you are uisng.

Thanks,

Pragati

[Anonymous]

@Pragati11 

Here is sample data

AcademicSemester	AcademicWeek	AverageSwipe
Spr	3	10,250.5
Fal	10	25,508.6
Sum	4	8,765.8