Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 sele...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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
    )
)))
VijayP's avatar
VijayP
Community Champion
6 years ago

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

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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))
 
 
I've used a calcualted column. Is this correct?
 
Thanks

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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
    )
)))
      • VijayP's avatar
        VijayP
        Community Champion
        6 years ago

        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