cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
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
Highlighted
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

Highlighted
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.

Highlighted
Anonymous
Not applicable

Re: Passing single parameter to multiple fields and filtering data

slicer.jpg

 

 

Highlighted
Anonymous
Not applicable

Re: Passing single parameter to multiple fields and filtering data

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

Highlighted
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
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
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors