SQL to DAX conversion

[Anonymous]

Hello Experts, I want to convert below SQL to a DAX query. The SQL 'cnt AS bucket' highlighted in query gives result as 2 buckets.  DAX query counts everything in 1 bucket instead of 2. My bucket 2 count is blank.  Can someone debug my DAX to find the issue. I have a month and year slicer selection in my report and selections are of June 2020.

SQL- 

SELECT cnt AS bucket,
hpc_busn_in_src,
Count(outlet_id) Ot_Cnt
FROM (SELECT outlet_id,
hpc_busn_in_src,
Count([deregister_dt]) Cnt
FROM [PRNTO_V2].[vw_fct_prnto_outlet_dergtn] WITH (nolock)
WHERE outlet_id IN (SELECT outlet_id
FROM [PRNTO_V2].[vw_fct_prnto_outlet_dergtn] WITH
(
nolock)
WHERE [deregister_dt] IS NOT NULL
AND deregister_flg = 'Y'
AND [deregister_dt] BETWEEN
'2021-06-01' AND '2021-06-30'
)
AND [deregister_dt] IS NOT NULL
AND deregister_flg = 'Y'
AND [deregister_dt]<= '2021-06-30'
GROUP BY outlet_id,
hpc_busn_in_src) T
GROUP BY cnt,
hpc_busn_in_src

DAX measure1 for bucket 1 -

CALCULATE(COUNTROWS(FILTER(SUMMARIZE(FILTER(ALL('Outlet Deregistration'), ('Outlet Deregistration'[Deregister_Dt])<=MAX('Date'[FullDate]) && ('Outlet Deregistration'[Deregister_Dt])<>BLANK()), 'Outlet Deregistration'[Outlet_Id], "DeregisteredTime", CALCULATE(COUNT('Outlet Deregistration'[Deregister_Dt]), 'Outlet Deregistration'[Deregister_Flg]="Y", DatesBetween('Date'[FullDate], Date(2021,06,01), Date(2021,06,30) ), 'Outlet Deregistration'[HPC_Busn_In_Src]="HPC") ), [DeregisteredTime]=1))

DAX measure2 for bucket >=2-
CALCULATE(COUNTROWS(FILTER(SUMMARIZE(FILTER(ALL('Outlet Deregistration'), ('Outlet Deregistration'[Deregister_Dt])<=MAX('Date'[FullDate]) && ('Outlet Deregistration'[Deregister_Dt])<>BLANK()), 'Outlet Deregistration'[Outlet_Id], "DeregisteredTime", CALCULATE(COUNT('Outlet Deregistration'[Deregister_Dt]), 'Outlet Deregistration'[Deregister_Flg]="Y", DatesBetween('Date'[FullDate], Date(2021,06,01), Date(2021,06,30) ), 'Outlet Deregistration'[HPC_Busn_In_Src]="HPC") ), [DeregisteredTime]>=2) )

[v-luwang-msft · Accepted solution]

Hi @Anonymous ，

Try like below :

Step 1， use the following measure:

bucket 1 = 
VAR TEST1 =
    CALCULATE (
        MAX ( vw_fct_prnto_outlet_dergtn[outlet_id] ),
        FILTER (
            vw_fct_prnto_outlet_dergtn,
            vw_fct_prnto_outlet_dergtn[deregister_flg] = "Y"
                && vw_fct_prnto_outlet_dergtn[deregister_dt] >= DATE ( 2021, 6, 1 )
                && vw_fct_prnto_outlet_dergtn[deregister_dt] <= DATE ( 2021, 6, 30 )
        )
    )
VAR TEST2 =
    CALCULATE (
        COUNT ( vw_fct_prnto_outlet_dergtn[deregister_dt] ),
        FILTER (
            vw_fct_prnto_outlet_dergtn,
            vw_fct_prnto_outlet_dergtn[outlet_id] = TEST1
                && vw_fct_prnto_outlet_dergtn[deregister_dt] <> BLANK ()
                && vw_fct_prnto_outlet_dergtn[deregister_dt] <= DATE ( 2021, 6, 30 )
                && vw_fct_prnto_outlet_dergtn[deregister_flg] = "Y"
        )
    )   
RETURN
    TEST2

This is get the sql result:

SELECT         outlet_id,
          hpc_busn_in_src,
    Count([deregister_dt]) Cnt
                             FROM [PRNTO_V2].[vw_fct_prnto_outlet_dergtn] WITH (nolock)
WHERE outlet_id IN 
(SELECT outlet_id
FROM [PRNTO_V2].[vw_fct_prnto_outlet_dergtn] WITH
(
nolock)
WHERE [deregister_dt] IS NOT NULL
AND deregister_flg = 'Y'
AND [deregister_dt] BETWEEN
'2021-06-01' AND '2021-06-30'
)
AND [deregister_dt] IS NOT NULL
AND deregister_flg = 'Y'
AND [deregister_dt]<= '2021-06-30'
GROUP BY outlet_id,
hpc_busn_in_src) T

Step 2,use the following dax to new table:

Table2 =
SUMMARIZE (
    'vw_fct_prnto_outlet_dergtn',
    vw_fct_prnto_outlet_dergtn[outlet_id],
    vw_fct_prnto_outlet_dergtn[hpc_busn_in_src],
    "bucket1", [bucket 1]
)

Then use the following dax to create a measure:

bucket2 = CALCULATE(COUNT(Table2[outlet_id]),ALLEXCEPT(Table2,Table2[bucket1],Table2[hpc_busn_in_src]))

Final out put:

You could download my pbix file if you need!

Wish it is helpful for you!

Best Regards

Lucien

[richbenmintz]

Hi @Anonymous ,

Can you please provide some sample data and the expected output from the sample, will really help in getting you the appropriate solution.

Thanks,