Can this SQL be converted to DAX

[Bokazoit]

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

[hackcrr · Accepted solution]

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]

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]

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