Dynamic Visual based on more than 1 selected value

[Anonymous]

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 

[VijayP]

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]

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

@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 · Accepted 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
    )
)))

[Icey]

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

[VijayP]

@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