Forum Discussion

bpdax's avatar
bpdax
New Member
2 years ago
Solved

Convert SQL To DAX

Hi,

 

I am very new to DAX and have to convert some SSRS into paginated reports using powerbi data source as the model has already been published. I am stuggling converting this stored procedure into DAX, any help would be very much appreciated. The parameters in this stored procedure are multi-valued ones:

 
SELECT DISTINCT
table.StaffID,
Team,
FirstName,
LastName,
CourseName,
DueOnDate
 
FROM [database].[dbo].[table]
INNER JOIN 
 
(
SELECT 
StaffID, 
SUM(TrainingTracked) TrainingTracked, 
SUM(Denominator) - SUM(Numerator) DemNumDiff, 
SUM(TrackedDenominator) - SUM(TrackedNumerator) TrackDemNumDiff,
CASE WHEN SUM(TrainingTracked) = 16 and SUM(TrackedDenominator) - SUM(TrackedNumerator) > 0 THEN 1
WHEN SUM(TrainingTracked) <> 16 and SUM(Denominator) - SUM(Numerator) > 0 THEN 1
 
ELSE 0
END NotComplaint
 
FROM [database].[dbo].[table]
WHERE [Compliant] = 0
AND Team IN (@Team)
AND Course IN (@Course)
GROUP BY StaffID
 
) A 
ON table.StaffID = a.StaffID
 
 
WHERE A.NotComplaint = 1
 
Thank you
 
DAX
PaginatedReports. ReportServer
SQL
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, bpdax 

    Based on your description, you want to implement DAX statements that have similar functionality to SQL statements, and the following are possible DAX statements. If it does not work, you can share the data without sensitive information, the expected effect and the logic to achieve the effect. Feel free for anything about this.

     

    VAR BaseTable = 
    CALCULATETABLE(
        'database'[table],
        'database'[Compliant] = 0,
        'database'[Team] IN @Team,
        'database'[Course] IN @Course
    )

VAR SummaryTable = 
    SUMMARIZE(
        BaseTable,
        'database'[StaffID],
        "TrainingTracked", SUM('database'[TrainingTracked]),
        "DemNumDiff", SUM('database'[Denominator]) - SUM('database'[Numerator]),
        "TrackDemNumDiff", SUM('database'[TrackedDenominator]) - SUM('database'[TrackedNumerator]),
        "NotComplaint", IF(
            [TrainingTracked] = 16 && [TrackDemNumDiff] > 0, 1,
            IF([TrainingTracked] <> 16 && [DemNumDiff] > 0, 1, 0)
        )
    )

VAR FinalTable = 
    FILTER(
        SummaryTable,
        [NotComplaint] = 1
    )

EVALUATE
    SELECTCOLUMNS(
        FinalTable,
        "StaffID", 'database'[StaffID],
        "Team", 'database'[Team],
        "FirstName", 'database'[FirstName],
        "LastName", 'database'[LastName],
        "CourseName", 'database'[CourseName],
        "DueOnDate", 'database'[DueOnDate]
    )

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

3 Replies

Replies have been turned off for this discussion
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, bpdax 

    Based on your description, you want to implement DAX statements that have similar functionality to SQL statements, and the following are possible DAX statements. If it does not work, you can share the data without sensitive information, the expected effect and the logic to achieve the effect. Feel free for anything about this.

     

    VAR BaseTable = 
    CALCULATETABLE(
        'database'[table],
        'database'[Compliant] = 0,
        'database'[Team] IN @Team,
        'database'[Course] IN @Course
    )

VAR SummaryTable = 
    SUMMARIZE(
        BaseTable,
        'database'[StaffID],
        "TrainingTracked", SUM('database'[TrainingTracked]),
        "DemNumDiff", SUM('database'[Denominator]) - SUM('database'[Numerator]),
        "TrackDemNumDiff", SUM('database'[TrackedDenominator]) - SUM('database'[TrackedNumerator]),
        "NotComplaint", IF(
            [TrainingTracked] = 16 && [TrackDemNumDiff] > 0, 1,
            IF([TrainingTracked] <> 16 && [DemNumDiff] > 0, 1, 0)
        )
    )

VAR FinalTable = 
    FILTER(
        SummaryTable,
        [NotComplaint] = 1
    )

EVALUATE
    SELECTCOLUMNS(
        FinalTable,
        "StaffID", 'database'[StaffID],
        "Team", 'database'[Team],
        "FirstName", 'database'[FirstName],
        "LastName", 'database'[LastName],
        "CourseName", 'database'[CourseName],
        "DueOnDate", 'database'[DueOnDate]
    )

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

    • bpdax's avatar
      bpdax
      New Member
      2 years ago

      Thank you for your help. I managed to write a similar piece of code, I just changed the last part of the code to a  NaturalLeftOuterJoin to get the rest of the things I needed.

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    2 years ago

    Hi bpdax ,

     

    Please try using below method

    EVALUATE
    VAR NotCompliantTable =
    SUMMARIZE (
    FILTER (
    VALUES ( 'database'[Team] ),
    [Compliant] = 0
    && [NotComplaint] = 1
    ),
    'database'[StaffID],
    "TrainingTracked", SUM ( 'database'[TrainingTracked] ),
    "DemNumDiff", SUM ( 'database'[Denominator] ) - SUM ( 'database'[Numerator] ),
    "TrackDemNumDiff", SUM ( 'database'[TrackedDenominator] ) - SUM ( 'database'[TrackedNumerator] ),
    "NotComplaint",
    SWITCH (
    TRUE (),
    SUM ( 'database'[TrainingTracked] ) = 16
    && SUM ( 'database'[TrackedDenominator] ) - SUM ( 'database'[TrackedNumerator] ) > 0, 1,
    SUM ( 'database'[TrainingTracked] ) <> 16
    && SUM ( 'database'[Denominator] ) - SUM ( 'database'[Numerator] ) > 0, 1,
    0
    )
    )
    EVALUATE
    SUMMARIZE (
    'database',
    'database'[StaffID],
    'database'[Team],
    'database'[FirstName],
    'database'[LastName],
    'database'[CourseName],
    'database'[DueOnDate]
    )
    EVALUATE
    FILTER (
    'database',
    'database'[StaffID]
    IN VALUES ( NotCompliantTable[StaffID] )
    )

     

    Please accept as solution and give kudos if it helps