Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, folks!
My goal is to implement a Measure that works an a "Filter on this Fisual" with an logical-test (1 for ResultTrue and 0 for ResultFalse) that returns 1 in my Table with a result, that shows only the rows with Sales in this selected Date Range.
First approach:
My first approach is I created a ParameterTable in my Dataset (Range 0-365 that means the Days for your understanding) with a related Measure max of this Range.
In my report I want to pick up this ParameterTable as an "Less than or equal to"-Date-Slicer-Filter and want to insert here a 0-365 Value for example 5.
Such as my MaxDate is 31.10.2019 and my Parameter in this Date-Slicer-Filter is 5 (Days), I only want to see only the rows in this Table from 31.10.2019 minus 5 Days (comes from[ParameterValueFromDataSet]) (26.10.2019) until 31.10.2019 plus 5 Days (5.11.2019)
This is not working:
IF (ABS(DATEDIFF(LASTDATE(Zeit[Date]);FIRSTNONBLANK(all(Zeit[Date]);TRUE());DAY)<='[ParameterValueFromDataSet]);1;0)
Second approach:
My second approach is to implement a second Date-Table in my Dataset that is filtered from my first DateTable (1-*). Then I go to my Report and set a Date-Slicer with the First-Date-Table with an between-range from 1.1.19 - 31.12.19 and a second Date-Slicer with my Second Date-Table to select here only 1 Date (such as 01.04.19).
Here, my "Filter on this Fisual"-Measure that works on a Table. It looks like:
Measure2 =
VAR selectedDate = CALCULATE(MAX(SecondDateTable[Date]);ALL(FirstDateTable[Date]))
RETURN
if(AND(MAX(FirstDateTable[Date]) >= SecondDateTable[Date] -[ParameterValueFromDataSet]; MAX(FirstDateTable[Date]) <= SecondDateTable[Date] +[ParameterValueFromDataSet]);1;0)
Example:
MAX(FirstDateTable[Date] = 31.12.2019
SecondDateTable[Date] = 01.04.2019
[ParameterValueFromDataSet] = 5
In the figurative sense:
Measure2 =
VAR selectedDate = 01.04.2019
RETURN
if(AND(Date>= 01.04.2019 minus 5 days that is in result 27.03.2019; Date <=01.04.2019 plus 5 days that is in result 06.04.2019 );1;0)
Here I want to shows only these rows wiht sales in my Table, that are between 27.03 - 06.04 although my Date Calender is showing more Days.
Thanks for your help.
Solved! Go to Solution.
Hi @tim_m15 ,
Would you please show us some sample data or table structure? According to your description, my understanding is that you have two slicer one for date , another one for ParameterValueFromDataSet. You want to show the values of a time period based on these two selected values If anything is misunderstood, please tell me.
I suggest you use the following measure :
Measure =
VAR selecteddate =
SELECTEDVALUE ( SecondDateTable[Date] )
VAR p =
SELECTEDVALUE ( ParameterValueFromDataSet[ParameterValueFromDataSet] )
RETURN
IF (
MAX ( FirstDateTable[Date] ) >= selecteddate - p
&& MAX ( FirstDateTable[Date] ) <= selecteddate + p,
1,
0
)
I created some test data, you can refer to the pbix file : https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EaTYxoZJG5VGoKewGV...
Best Regards,
Dedmon Dai
Hi Dedmon,
thanks for your reply and your tip looks helpful!
I could now solve this challenge myself. I implement a second Date Table as a Slicer as I have described and another Slicer with the Parametertable and write the following measure in a function of a visual-filter in a true-/false-Condition
Measure:
VAR SelectedDate = CALCULATE( MAX( Date[SecondDateTable] ); ALL( Date[FirstDateTable] ) )
RETURN
IF( AND( MAX(Sales[BookingDate] ) >= SelectedDate - [ParameterTableValue] ; MAX(Sales[BookingDate]) <= SelectedDate + [ParameterTableValue] );
1;
0)
Thereafter I put the Sales and the BookingDate in my Table and filter the visual-filter with "1" and it works.
Best Regards,
Tim
Hi @tim_m15 ,
Would you please show us some sample data or table structure? According to your description, my understanding is that you have two slicer one for date , another one for ParameterValueFromDataSet. You want to show the values of a time period based on these two selected values If anything is misunderstood, please tell me.
I suggest you use the following measure :
Measure =
VAR selecteddate =
SELECTEDVALUE ( SecondDateTable[Date] )
VAR p =
SELECTEDVALUE ( ParameterValueFromDataSet[ParameterValueFromDataSet] )
RETURN
IF (
MAX ( FirstDateTable[Date] ) >= selecteddate - p
&& MAX ( FirstDateTable[Date] ) <= selecteddate + p,
1,
0
)
I created some test data, you can refer to the pbix file : https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EaTYxoZJG5VGoKewGV...
Best Regards,
Dedmon Dai
Hi Dedmon,
thanks for your reply and your tip looks helpful!
I could now solve this challenge myself. I implement a second Date Table as a Slicer as I have described and another Slicer with the Parametertable and write the following measure in a function of a visual-filter in a true-/false-Condition
Measure:
VAR SelectedDate = CALCULATE( MAX( Date[SecondDateTable] ); ALL( Date[FirstDateTable] ) )
RETURN
IF( AND( MAX(Sales[BookingDate] ) >= SelectedDate - [ParameterTableValue] ; MAX(Sales[BookingDate]) <= SelectedDate + [ParameterTableValue] );
1;
0)
Thereafter I put the Sales and the BookingDate in my Table and filter the visual-filter with "1" and it works.
Best Regards,
Tim
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |