Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

@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

Hi @Anonymous

 

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

 

Regards,

Affan

Anonymous
Not applicable

@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

 

Anonymous
Not applicable

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

 

 

 

@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

Anonymous
Not applicable

PBIX link

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

Anonymous
Not applicable

slicer.jpg

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.