Sub queries with one slicer on 2 different date columns

[Anonymous]

I'm building a new report in Power BI. I'm migrating the report from another reporting application.

Below is the how the query is written. I'm not exactly sure how I can achieve the same in Power BI.

SELECT
cust_no,amt

from ACCT_PREMIUM

where
EFF_DT <= TO_DATE(&&date_param,'mm/dd/yyyy HH24:MI:SS')
AND EXP_DT > TO_DATE(&&date_param,'mm/dd/yyyy HH24:MI:SS')

AND CUST_NO IN
(SELECT CUST_NO from

(select cust_no, sum(amt) as amt from ACCT_PREMIUM
where
EFF_DT <= TO_DATE(&&date_param,'mm/dd/yyyy HH24:MI:SS')
AND EXP_DT > TO_DATE(&&date_param,'mm/dd/yyyy HH24:MI:SS')
group by cust_no)
where amt >= &&amt_filter AND amt <= &&amt_filter)

First challenge is I need to have only one date slicer which needs to filter 2 columns(EFF_DT and EXP_DT). I was able to get a solution from the below post.

https://community.powerbi.com/t5/Desktop/Using-one-slicer-on-2-different-date-columns/m-p/633468

Now I'm not sure how I can achieve the two sub queries.

Thanks in Advance.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

From your formula:

amt >= &&amt_filter(1) AND amt <= &&amt_filter(2)

It means amt =&&amt_filter  if &&amt_filter is the same one for the (1) and (2).

I create a "parameter" table by "What-if" paramter,  use the "parameter" to replace &&amt_filter.

Then Create measures

Measure =
IF (
    MAX ( ACCT_PREMIUM[EFF_DT] ) <= SELECTEDVALUE ( 'calendar'[Date] )
        && MAX ( ACCT_PREMIUM[EXP_DT] ) > SELECTEDVALUE ( 'calendar'[Date] ),
    1,
    0
)

Measure 2 =
CALCULATE (
    SUM ( ACCT_PREMIUM[amt] ),
    FILTER ( ALLEXCEPT ( ACCT_PREMIUM, ACCT_PREMIUM[cust_no] ), [Measure] = 1 )
)

Measure 3 = IF([Measure 2]=[Parameter Value],1,0)

Measure 4 = IF([Measure]=1&&[Measure 3]=1,1,0)

Add [Meaure4] in the visual level filter of the table as above.

Please refer to my pbix.

Additionally, i will show how your formula can transform to my measure below:

SELECT
cust_no,amt

from ACCT_PREMIUM        ->   Measure 4

where
EFF_DT <= TO_DATE(&&date_param,'mm/dd/yyyy HH24:MISmiley FrustratedS')
AND EXP_DT > TO_DATE(&&date_param,'mm/dd/yyyy HH24:MISmiley FrustratedS')   ->Measure

AND CUST_NO IN
(SELECT CUST_NO from

(select cust_no, sum(amt) as amt from ACCT_PREMIUM
where
EFF_DT <= TO_DATE(&&date_param,'mm/dd/yyyy HH24:MISmiley FrustratedS')
AND EXP_DT > TO_DATE(&&date_param,'mm/dd/yyyy HH24:MISmiley FrustratedS')
group by cust_no)                   ->                                   Measure 2
where amt >= &&amt_filter AND amt <= &&amt_filter)       ->              Measure 3

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Is this problem sloved?

If not, please let me know.

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

From your formula:

amt >= &&amt_filter(1) AND amt <= &&amt_filter(2)

It means amt =&&amt_filter  if &&amt_filter is the same one for the (1) and (2).

I create a "parameter" table by "What-if" paramter,  use the "parameter" to replace &&amt_filter.

Then Create measures

Measure =
IF (
    MAX ( ACCT_PREMIUM[EFF_DT] ) <= SELECTEDVALUE ( 'calendar'[Date] )
        && MAX ( ACCT_PREMIUM[EXP_DT] ) > SELECTEDVALUE ( 'calendar'[Date] ),
    1,
    0
)

Measure 2 =
CALCULATE (
    SUM ( ACCT_PREMIUM[amt] ),
    FILTER ( ALLEXCEPT ( ACCT_PREMIUM, ACCT_PREMIUM[cust_no] ), [Measure] = 1 )
)

Measure 3 = IF([Measure 2]=[Parameter Value],1,0)

Measure 4 = IF([Measure]=1&&[Measure 3]=1,1,0)

Add [Meaure4] in the visual level filter of the table as above.

Please refer to my pbix.

Additionally, i will show how your formula can transform to my measure below:

SELECT
cust_no,amt

from ACCT_PREMIUM        ->   Measure 4

where
EFF_DT <= TO_DATE(&&date_param,'mm/dd/yyyy HH24:MISmiley FrustratedS')
AND EXP_DT > TO_DATE(&&date_param,'mm/dd/yyyy HH24:MISmiley FrustratedS')   ->Measure

AND CUST_NO IN
(SELECT CUST_NO from

(select cust_no, sum(amt) as amt from ACCT_PREMIUM
where
EFF_DT <= TO_DATE(&&date_param,'mm/dd/yyyy HH24:MISmiley FrustratedS')
AND EXP_DT > TO_DATE(&&date_param,'mm/dd/yyyy HH24:MISmiley FrustratedS')
group by cust_no)                   ->                                   Measure 2
where amt >= &&amt_filter AND amt <= &&amt_filter)       ->              Measure 3

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.