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