cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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
affan Solution Sage
Solution Sage

Re: Passing single parameter to multiple fields and filtering data

@Anonymous

 

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

 

View solution in original post

7 REPLIES 7
affan Solution Sage
Solution Sage

Re: Passing single parameter to multiple fields and filtering data

Hi @Anonymous

 

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

 

Regards,

Affan

Anonymous
Not applicable

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.

Anonymous
Not applicable

Re: Passing single parameter to multiple fields and filtering data

slicer.jpg

 

 

Anonymous
Not applicable

Re: Passing single parameter to multiple fields and filtering data

affan Solution Sage
Solution Sage

Re: Passing single parameter to multiple fields and filtering data

@Anonymous

 

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

 

View solution in original post

Anonymous
Not applicable

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

 

 

 

Highlighted
affan Solution Sage
Solution Sage

Re: Passing single parameter to multiple fields and filtering data

@Anonymous

 

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

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors