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

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

6 Replies

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