Forum Discussion
Calculation based on 2 or more variables (slicer/parameter?)
Hi Harom
Thank you for reaching out microsoft fabric community forum.
could you please provide some sample data.so that we can look into it more effienciently.
Thank you.
bhanu_gautam
Thank you for your quick reply!
When I go to Modeling > New Parameter , I get two options: Numeric Range and Fields. In both cases I'm not able to create my own two options.
- bhanu_gautam
Super User
Harom , You can do that like
Create a Currency Selection Table:
Go to the "Home" tab and select "Enter Data".
Create a table with a single column named "Currency" and add the values "EUR" and "USD".
Name this table "CurrencySelection".
Create a Period Selection Table:Similarly, create another table for period selection with the desired periods (e.g., YTD, L12M, etc.).
Name this table "PeriodSelection".
Create Relationships:Ensure that these tables are not related to your main data table. They will be used solely for slicers.
Create Measures for Currency and Period Selection:Selected Currency =SELECTEDVALUE(CurrencySelection[Currency])and create similar for other and use them in field parameterbhanu_gautam the good news, the solution with the tables works and I managed to get all columns and measures working without errors showing, the bad news is that the new created columns stay empty. When I put the slicer in the report and a table with the calculation, it works:
Some other screenshots:
- HarishKM
Harom Hey,
you can create your all measure then create a field parameter for the same as below image.
expected output as below.Thanks
Harish M
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query- HarishKM
Harom Hey,
try below things1st table
CurrencySelection =
DATATABLE("Currency", STRING, {
{"USD"},
{"EUR"},
{"GBP"}
})2nd table
PeriodSelection =
DATATABLE("Period", STRING, {
{"MTD"},
{"YTD"},
{"QTD"},
{"Full Year"},
{"PY MTD"},
{"PY YTD"}
})
once you have above 2 disconnected (Newly created table) then you have create below measureSelectedSales =
VAR SelectedCurrency = SELECTEDVALUE(CurrencySelection[Currency])
VAR SelectedPeriod = SELECTEDVALUE(PeriodSelection[Period])-- Step 1: Apply currency conversion logic (example rate logic)
VAR SalesInCurrency =
SWITCH(
SelectedCurrency,
"USD", [Sales_USD],
"EUR", [Sales_EUR],
"GBP", [Sales_GBP],
[Sales_USD] -- default
)-- Step 2: we have to write a period logic now
RETURN
SWITCH(
SelectedPeriod,
"MTD", CALCULATE(SalesInCurrency, DATESMTD(DateTable[Date])),
"YTD", CALCULATE(SalesInCurrency, DATESYTD(DateTable[Date])),
"QTD", CALCULATE(SalesInCurrency, DATESQTD(DateTable[Date])),
"Full Year", CALCULATE(SalesInCurrency, YEAR(DateTable[Date]) = YEAR(TODAY())),
"PY MTD", CALCULATE(SalesInCurrency, DATESMTD(SAMEPERIODLASTYEAR(DateTable[Date]))),
"PY YTD", CALCULATE(SalesInCurrency, DATESYTD(SAMEPERIODLASTYEAR(DateTable[Date]))),
SalesInCurrency -- default
)
above solution is fully scaleble for future.Thanks
Harish M
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your queryHarishKM
I've got the Table and selected value part working. When I want to apply this in for example the VAR SalesinCurrency the way you described (I've added a New Column with the calculation), there is no result in the column.
-- Step 1: Apply currency conversion logic (example rate logic)
VAR SalesInCurrency =
SWITCH(
SelectedCurrency,
"USD", [Sales_USD],
"EUR", [Sales_EUR],
"GBP", [Sales_GBP],
[Sales_USD] -- default
)
My logic:Sales Value Currency =SWITCH('Par Currency'[Currency Selected],"EUR", Query2[Amount Value],"USD", Query2[Amount Value USD])
