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].[FactBetalingslinjer]
	WHERE
		BetalingsNr = 1 
		AND AbonnementsNr = 1
		AND DatoKeyStoettePeriode BETWEEN @StartingDate AND @EndingDate
		AND BetalingsmetodeKey NOT IN (-1,-24)
		AND StøttetypeKey IN (24,25,26)
	)

select
	[Customer] = fb.[DonorNummer],
	[Category] = ds.Støttetype,
	[pcs] = COUNT(fb.StøttetypeKey),
	[Amount] = SUM([#Bogført betalt beløb])
from [Medlemsdata].[FactBetalingslinjer] fb
JOIN FirstSubscription fs ON fb.[DonorNummer] = fs.[DonorNummer] AND fb.[DatoKeyStoettePeriode] <= fs.[DatoKeyStoettePeriode]
JOIN Medlemsdata.DimStøttetype ds ON fb.StøttetypeKey = ds.StøttetypeKey
GROUP BY
	fb.[DonorNummer],
	ds.Støttetype
ORDER BY
	1

 

Startingdate and EndingDate is just the date slider, so whatever periode I choose the result reflects that

 

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

     

3 Replies

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

  • hackcrr's avatar
    hackcrr
    Memorable Member
    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