CODEWITHSUNDEEP
powerbidax
Louis Chopard
Louis Chopard

Reputation: 334

Why can't I filter a column based on a parameters table's value?

I wanna filter a Date using a table parameters.

First, I created a Parameters table like this :

enter image description here

Here is the table's values :

enter image description here

And then I created a Slicer with the table parameters values as a list :

enter image description here

So what I want is to filter my Date table based on which value is selected on the slicer.

The Date table looks like this :

v_DATE
Date

Date is a "date" type.

Here are some sample values :

Date
13.10.21
14.10.21
15.10.21

The Date table is used on a bar chart with values that came from an article table.

enter image description here

So what I want is when I click on the Last30Days it filters me all the values from the current date to 30 days ago.

I tried this :

On my Date column, I right clicked and clicked on "New measure".

Then I wrote a DAX code like this :

Measure = CALCULATE(IF(SELECTEDVALUE(Parametres[Selection_date]) = "Last30Days", filter(v_Date, v_Date[Date] = "15.10.21")))`

I used 15.10.21 as an example value to test my query. But It didn't work, the date isn't filtered.

So what I am doing wrong ? And how can I write my 3 parameters?

EDIT

How can I add the values that are not in the last 30 days ?

enter image description here

Upvotes: 0

Views: 793

Answers (2)

Joao Leal
Joao Leal

Reputation: 5542

In your measure formula you need to add the expression that you want to calculate, so if it's a sum or a count of records that's what you pick.

One way of doing it could be:

Measure = Count(Data[Records])

Measure with Param = 
  var selectedParam = SELECTEDVALUE(Parametres[Selection_date])

  var dateFilter = FILTER(Dates, 
     (selectedParam = "Last30Days" && Dates[Date] = DATE(2021,01,19)) ||
     (selectedParam = "MonthToDate" && Dates[Date] > DATE(2021,01,19)) ||
     (selectedParam = "YearToDate" && Dates[Date] > DATE(2021,01,19)) ||
     (ISBLANK(selectedParam)))
  //note: the date filters are just an example, not how you would actually slice these.
  
  return CALCULATE([Measure], dateFilter)

But you should look into Calculation Groups, as it is the nicest way to do what you're after. https://www.sqlbi.com/blog/marco/2020/07/15/creating-calculation-groups-in-power-bi-desktop/

Upvotes: 0

Yash
Yash

Reputation: 97

You haven't mentioned the measure/column name which you are applying the filter, so I'm assuming it as a Measure([Measure_Value]).

If your user is only selecting only single option at a time, you can use a switch case to filter the expression based on slicer value.

You can calculate the current date using TODAY() and on the basis of that you can calculate other time values like this:

var _month = MONTH(_today)
var _year = YEAR(_today)
var _last_30_days = _today - 30

On the basis of these values and switch case, you can create the measure:

Measure =
 
var _today = TODAY()
var _month = MONTH(_today)
var _year = YEAR(_today)
var _last_30_days = _today - 30

return SWITCH(SELECTEDVALUE(Parametres[Selection_date]),
"MonthToDate",CALCULATE([Measure_Value],FILTER(ALL(v_Date),MONTH('v_Date'[Date])=_month && YEAR('v_Date'[Date])=_year)),
"Last30Days",CALCULATE([Measure_Value],FILTER(ALL(v_Date),'v_Date'[Date]>_last_30_days)),
"YearToDate",CALCULATE([Measure_Value],FILTER(ALL(v_Date),YEAR('v_Date'[Date])=_year)),
BLANK())

Upvotes: 2

Related Questions