Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
I have got the following result.
If this helped you, please mark this post as an accepted solution and like to give KUDOS .
Regards,
Affan
Hi @Anonymous
Can you share the pbix or some samples from your data and the desired result.
Regards,
Affan
@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
I have got the following result.
If this helped you, please mark this post as an accepted solution and like to give KUDOS .
Regards,
Affan
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 HASONEFILTER
@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
Thanks alot affan for reply here im attaching my PBIX file and Expected Output in it.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |