Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Anonymous
Not applicable

Dynamic Visual based on more than 1 selected value

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:

Table 1.PNGTable 2.PNG

 

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 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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
    )
)))

View solution in original post

6 REPLIES 6
VijayP
Super User
Super User

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

 




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Anonymous
Not applicable

@VijayP  Thank you for your reply!

 

I tried the formula below, though I'm getting an error.

 

SWITCH(TRUE(),
SELECTEDVALUE(
AND('Aggregation Option'[Display Value] ="Per TCE", 'Aggregation Option'[Display Value] = "YTD"), [YTD TCE Price],
AND('Aggregation Option'[Display Value] ="Per TCE", 'Aggregation Option'[Display Value] ="LAST Month"), [Last Month TCE Price],

1))
 
error.PNG
 
I've used a calcualted column. Is this correct?
 
Thanks

 

Anonymous
Not applicable

@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?

Anonymous
Not applicable

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
    )
)))
Icey
Community Support
Community Support

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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.