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.
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.
Solved! Go to 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.
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.
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.
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.
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 |
---|---|
111 | |
94 | |
83 | |
67 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |