Forum Discussion

Bokazoit's avatar
Bokazoit
Continued Contributor
2 years ago
Solved

Can this SQL be converted to DAX

This SQL:   DECLARE @StartingDate AS DATE = '20201001' DECLARE @EndingDate AS DATE = GETDATE() WITH FirstSubscription AS ( SELECT [DonorNummer], [DatoKeyStoettePeriode] FROM [Medlemsdata]...
Need Help
  • hackcrr's avatar
    hackcrr
    2 years ago

    Hi, Bokazoit 

    To convert the provided SQL query to DAX, you can follow the steps below:

    Create a calculation table in Power BI to replicate FirstSubscriptionCTE:

    FirstSubscription = 
FILTER (
    Medlemsdata[FactBetalingslinjer],
    Medlemsdata[FactBetalingslinjer][BetalingsNr] = 1
    && Medlemsdata[FactBetalingslinjer][AbonnementsNr] = 1
    && Medlemsdata[FactBetalingslinjer][DatoKeyStoettePeriode] >= MIN(DateTable[Date])
    && Medlemsdata[FactBetalingslinjer][DatoKeyStoettePeriode] <= MAX(DateTable[Date])
    && NOT (Medlemsdata[FactBetalingslinjer][BetalingsmetodeKey] IN {-1, -24})
    && (Medlemsdata[FactBetalingslinjer][StøttetypeKey] IN {24, 25, 26})
)

    Create metrics for counts and sums:

    CustomerCategoryCount = 
CALCULATE (
    COUNT ( Medlemsdata[FactBetalingslinjer][StøttetypeKey] ),
    FILTER (
        Medlemsdata[FactBetalingslinjer],
        Medlemsdata[FactBetalingslinjer][DonorNummer] IN VALUES (FirstSubscription[DonorNummer])
        && Medlemsdata[FactBetalingslinjer][DatoKeyStoettePeriode] <= VALUES (FirstSubscription[DatoKeyStoettePeriode])
    )
)

CustomerCategoryAmount = 
CALCULATE (
    SUM ( Medlemsdata[FactBetalingslinjer][#Bogført betalt beløb] ),
    FILTER (
        Medlemsdata[FactBetalingslinjer],
        Medlemsdata[FactBetalingslinjer][DonorNummer] IN VALUES (FirstSubscription[DonorNummer])
        && Medlemsdata[FactBetalingslinjer][DatoKeyStoettePeriode] <= VALUES (FirstSubscription[DatoKeyStoettePeriode])
    )
)

    To display the results, create a table visual object in Power BI and add the following fields:
    Customer from Medlemsdata[FactBetalingslinjer][DonorNummer]
    Category from Medlemsdata[DimStøttetype][Støttetype]
    pcs as a measure of CustomerCategoryCount
    Amount as a measure of CustomerCategoryAmount

    If desired, you can also create a summary table in DAX for visual use:

    SummaryTable = 
SUMMARIZE (
    Medlemsdata[FactBetalingslinjer],
    Medlemsdata[FactBetalingslinjer][DonorNummer],
    Medlemsdata[DimStøttetype][Støttetype],
    "pcs", [CustomerCategoryCount],
    "Amount", [CustomerCategoryAmount]
)

    By following these steps, you can replicate the functionality of SQL queries in Power BI using DAX

     

    hackcrr

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

     

rajendraongole1's avatar
rajendraongole1
Super User
2 years ago

Hi Bokazoit - Yes, it is possible but with slight modifications in your sql code

as it wont support DECLARE Instead, we directly define variables using VAR.
We emulate the functionality of the WITH clause by using DEFINE to define calculated tables and measures. to perform group by we will use SUMMARIZE function to group the data and calculate aggregates based on the defined measures.

 

You can define a calculated table for FirstSubscription using the same date paramters. 
DEFINE
VAR StartingDate = DATE(2020, 10, 01)
VAR EndingDate = TODAY()
EVALUATE
FILTER (
ADDCOLUMNS (
SUMMARIZECOLUMNS (
'Medlemsdata'[DonorNummer],
'Medlemsdata'[DatoKeyStoettePeriode]
),
"@BetalingsNr", CALCULATE(COUNTROWS('Medlemsdata'), 'Medlemsdata'[BetalingsNr] = 1),
"@AbonnementsNr", CALCULATE(COUNTROWS('Medlemsdata'), 'Medlemsdata'[AbonnementsNr] = 1),
"@BetalingsmetodeKey", VALUES('Medlemsdata'[BetalingsmetodeKey]),
"@StøttetypeKey", VALUES('Medlemsdata'[StøttetypeKey])
),
[@BetalingsNr] = 1
&& [@AbonnementsNr] = 1
&& NOT ( [@BetalingsmetodeKey] IN { -1, -24 } )
&& [@StøttetypeKey] IN { 24, 25, 26 }
&& 'Medlemsdata'[DatoKeyStoettePeriode] >= StartingDate
&& 'Medlemsdata'[DatoKeyStoettePeriode] <= EndingDate
)

 

Define a measure for calculating pcs:
DEFINE
MEASURE 'Medlemsdata'[pcs] =
COUNTROWS(
FILTER(
'Medlemsdata',
'Medlemsdata'[DonorNummer] IN VALUES('Medlemsdata'[DonorNummer])
)
)

calculate a measure for calculating Amount as below:
DEFINE
MEASURE 'Medlemsdata'[Amount] =
SUMX(
'Medlemsdata',
'Medlemsdata'[#Bogført betalt beløb]
)

EVALUATE
SUMMARIZE(
'Medlemsdata',
'Medlemsdata'[DonorNummer],
'Medlemsdata'[Støttetype],
"pcs", [pcs],
"Amount", [Amount]
)
ORDER BY
'Medlemsdata'[DonorNummer]

 

check the above Dax code and let know 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

  • Bokazoit's avatar
    Bokazoit
    Continued Contributor
    2 years ago

    When I enter Your code it says the syntax for DEFINE is wrong?