Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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) )
Solved! Go to 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
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
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,
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |