cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vijendarreddy Regular Visitor
Regular Visitor

Passing single parameter to multiple fields and filtering data

Hi im try to compar 3 fields with single input value.

up on selection on single value that need to be passed to 3 fields as below ex:

InputVal=10

field1<=10

field2>=10

field3<=10

result should impact on my existing table visual which has some 20 fields from different tables.


here im bringing data from SSAS tabular with DIRECT QUERY MODE ON

so i cant do much tweeking on desktop side,i can write a measure only.

 

_selected=SelectedValue(mytbl,mytblCol)---ex 10 i selected

 

_PassSelected=Filter(myTbl,myfield1<=_selected && myField2>=_selected && myFiled3<=_selected)--here im trying to pass the selected value dynamically. plz help in writing DAX expression

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
affan Established Member
Established Member

Re: Passing single parameter to multiple fields and filtering data

@vijendarreddy

 

You can use the below measure

 

Measure = var _f1=FIRSTNONBLANK(tbl1[Field1],1)
var _f2=FIRSTNONBLANK(tbl1[Field2],1)
var _f3=FIRSTNONBLANK(tbl1[Field3],1)

var _checkf1=IF(SELECTEDVALUE(tbl2[inputField])<=_f1,1,0)
var _checkf2 = IF(SELECTEDVALUE(tbl2[inputField])<=_f2,1,0)
var _checkf3= IF(SELECTEDVALUE(tbl2[inputField])>=_f3,1,0)
return
_checkf1+_checkf2+_checkf3

Then use this measure in the visual filter and filter the values 3

 

filter rows on multiple columns1.png

 

I have got the following result.

filter rows on multiple columns.png

 

 

 

 

 

 

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

 

Regards,

Affan

 

7 REPLIES 7
affan Established Member
Established Member

Re: Passing single parameter to multiple fields and filtering data

Hi @vijendarreddy

 

Can you share the pbix or some samples from your data and the desired result.

 

Regards,

Affan

vijendarreddy Regular Visitor
Regular Visitor

Re: Passing single parameter to multiple fields and filtering data

PBIX link

Thanks alot affan for reply here im attaching my PBIX file and Expected Output in it.

vijendarreddy Regular Visitor
Regular Visitor

Re: Passing single parameter to multiple fields and filtering data

slicer.jpg

 

 

vijendarreddy Regular Visitor
Regular Visitor

Re: Passing single parameter to multiple fields and filtering data

Highlighted
affan Established Member
Established Member

Re: Passing single parameter to multiple fields and filtering data

@vijendarreddy

 

You can use the below measure

 

Measure = var _f1=FIRSTNONBLANK(tbl1[Field1],1)
var _f2=FIRSTNONBLANK(tbl1[Field2],1)
var _f3=FIRSTNONBLANK(tbl1[Field3],1)

var _checkf1=IF(SELECTEDVALUE(tbl2[inputField])<=_f1,1,0)
var _checkf2 = IF(SELECTEDVALUE(tbl2[inputField])<=_f2,1,0)
var _checkf3= IF(SELECTEDVALUE(tbl2[inputField])>=_f3,1,0)
return
_checkf1+_checkf2+_checkf3

Then use this measure in the visual filter and filter the values 3

 

filter rows on multiple columns1.png

 

I have got the following result.

filter rows on multiple columns.png

 

 

 

 

 

 

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

 

Regards,

Affan

 

vijendarreddy Regular Visitor
Regular Visitor

Re: Passing single parameter to multiple fields and filtering data

Thank you verymuch Affan,

have another issue im facing with real time data.

as im using SSAS DirectQuery mode on also August2018 onpremise version im using.

may be for some reason SELECTEDVALUE function is not working.

instead i tried ALLSELECTED as below.

here issue is when i dont select any thing i should get data with out Filter,but my visual shows error.

because ALLSELECTED push all the values to compar.

any idea to overcome this.

or shal i try any other functions like HASONEFILTERSlicerSelected.png

 

 

 

affan Established Member
Established Member

Re: Passing single parameter to multiple fields and filtering data

@vijendarreddy

 

You can try this 

 

Measure = var _f1=FIRSTNONBLANK(tbl1[Field1],1)
var _f2=FIRSTNONBLANK(tbl1[Field2],1)
var _f3=FIRSTNONBLANK(tbl1[Field3],1)
var _selvalue= FIRSTNONBLANK(tbl2[inputField],1)
var _checkf1=IF(_selvalue<=_f1,1,0)
var _checkf2 = IF(_selvalue<=_f2,1,0)
var _checkf3= IF(_selvalue>=_f3,1,0)
return
_checkf1+_checkf2+_checkf3

 

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

 

Regards,

Affan