Dynamic Measure based on Filter Selection Direct Query

[Anonymous]

Hi all,

I'm working with direct query to connect SAP BW, I'm unable to use most of features of Power BI because of Direct Query.

Is it possible to create dynamic measure based on filter ?
I mean For ex I have year filter and i have 2 measure like Selected Year and Selected Year -1
if I select 2020 it it must be return 2020 and 2019
if I select 2018 it must be return 2018 and 2017

As you see i can't create table or something like that. Is there anyone have idea ? 
 

[Anonymous]

Hi @Anonymous,

If you want to achieve selector effect on your records instead of filter, you need to use not connect table(not has relationship to the current table) fields on slicer. Then you can use dax function to extract selected values as a condition to calculate in your measures.

If above not help, please share some dummy data with a similar data structure and expected result to help us clarify your scenario and test to conding formula:

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous , In such cases it is better to a separate date/year table

try like

measure =
var _max = maxx(allselected(Date),Date[Year])
return
calculate(sum(Table[Data]), filter(all(Date),Date[Year]<=_max && Date[Year]>=_max-1 ))

Otherwise all will remove other filters too

[Anonymous]

Hi @amitchandak,

As I write in top, I'm working with bw direct query and I'm unable to create separate table.

Also I'm unable to change type of dimension.