Last answered:

09 Mar 2023

Posted on:

09 Mar 2023

1

Resolved: Another way to create dynamic filter

I tried to write the function by myself and came up with a better way to write it. We could break down the function into smaller chunks:

1. Calculate sum of sales amount

2. Define logic if a specific currency is chosen 

3. Use calculate to return sales amount dynamically to the chosen currency

4. Build up the function (add the condition in case no currency is chosen)


So my answer is:


SalesAmountSelectedCurrency = 


var SalesAmount = SUM(fact_InternetSales[SalesAmount])


var SelectedCurrency = IF(ISFILTERED(dim_Currency[CurrencyAlternateKey]), VALUES(dim_Currency[CurrencyAlternateKey]), BLANK())


var SalesAmountbySelectedCurrency = CALCULATE(SalesAmount, dim_Currency[CurrencyAlternateKey] = SelectedCurrency)


RETURN IF(ISBLANK(SelectedCurrency), "No Currency Selected", SalesAmountbySelectedCurrency)


Please give me further feedbacks if I do something wrong. Thank you!

2 answers ( 1 marked as helpful)
Instructor
Posted on:

09 Mar 2023

0

My only concern is using VALUES(dim_Currency[CurrencyAlternateKey]), BLANK()), not sure if DAX will approve the syntax since values returns a range instead of a single value. But if you are not running into any errors, feel free to use that code

Posted on:

09 Mar 2023

0

It worked. Besides, thanks a lot for your intruction so far

Submit an answer