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.
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!
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |