Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
Hi all,
I'm trying to get dynamic measures to work where users can choose 1 option from "Display Value" table and 1 option from "Date Range" table.
For example:
If "Per TCE" & "YTD" is selected then will display YTD TCE Price
If "Per TCE" & "Last Month" is selected then will display Last Month Price
If "Total Amount" & "YTD" is selected then will display YTD Revenue
etc
I've created my 2 tables:
I can get it to work if if there is only one table but with the 2nd table is making it difficult.
Dynamic YTD or Month - Price =
IF(
SELECTEDVALUE('Date Range Option'[Date Range]) = "YTD",
([YTD TCE Price]),
([Last Month TCE Price])
)
Ideally another line saying "if(selected value = "TCE") Then " "
I tried doing an calculated column IF Statement by joining the the two tables together, but ends up returning blank.
Dynamic IF Statement =
IF('Aggregation Option'[Display Value] = "YTD",
IF('Aggregation Option'[Display Value] = "Per TCE",
[YTD TCE Price],
IF('Aggregation Option'[Display Value] = "Last Month",
IF('Aggregation Option'[Display Value] = "Per TCE",
[Last Month TCE Price],
1
))))
Any help would be great!
Thanks
Solved! Go to Solution.
managed to get it working using the code below:
Dynamic Options =
IF(
AND(
SELECTEDVALUE('Date Range Option'[Date Range])= "Last Month",
SELECTEDVALUE('Aggregation Option'[Display Value]) = "Per TCE"
),
[Last Month TCE Price],
IF(
AND(
SELECTEDVALUE('Date Range Option'[Date Range])= "Last Month",
SELECTEDVALUE('Aggregation Option'[Display Value]) = "Total Amount"
),
[Last Month Revenue NZD],
IF(
AND(
SELECTEDVALUE('Date Range Option'[Date Range])= "YTD",
SELECTEDVALUE('Aggregation Option'[Display Value]) = "Total Amount"
),
[YTD Sales NZD],
IF(
AND(
SELECTEDVALUE('Date Range Option'[Date Range])= "YTD",
SELECTEDVALUE('Aggregation Option'[Display Value]) = "Per TCE"
),
[YTD TCE Price],
0
)
)))
Use SWITCH Funciton
=SWITH(TRUE(),
SELECTEDVALUE(
AND([display value]="Per TCE", [Date Range]="YTD"), [YTD TCE PRICE) ,
AND([display value]="Per TCE", [Date Range]="LAST Month"), [Last Month TCE PRICE) ,
1))
This will help you
Proud to be a Super User!
@VijayP Thank you for your reply!
I tried the formula below, though I'm getting an error.
@VijayP Another question, in calculated column why can't I pick the date range table in my formula, like you suggested? Is it because I've no relationship between the two?
managed to get it working using the code below:
Dynamic Options =
IF(
AND(
SELECTEDVALUE('Date Range Option'[Date Range])= "Last Month",
SELECTEDVALUE('Aggregation Option'[Display Value]) = "Per TCE"
),
[Last Month TCE Price],
IF(
AND(
SELECTEDVALUE('Date Range Option'[Date Range])= "Last Month",
SELECTEDVALUE('Aggregation Option'[Display Value]) = "Total Amount"
),
[Last Month Revenue NZD],
IF(
AND(
SELECTEDVALUE('Date Range Option'[Date Range])= "YTD",
SELECTEDVALUE('Aggregation Option'[Display Value]) = "Total Amount"
),
[YTD Sales NZD],
IF(
AND(
SELECTEDVALUE('Date Range Option'[Date Range])= "YTD",
SELECTEDVALUE('Aggregation Option'[Display Value]) = "Per TCE"
),
[YTD TCE Price],
0
)
)))
Hi @Anonymous ,
Glad to hear that this problem has been solved. You may help accept the solution above. Your contribution is highly appreciated.
Best Regards,
Icey
@Anonymous Hope my idea worked and if you think so, please select my answer as solution and share your Kudos.
Also you can watch my Vidoes at www.youtube.com/perepavijay and visit my LinkedIn Profile
Regards
Vijay Perepa
Proud to be a Super User!
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.