Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I am trying to filter out a particular value from a table using DAX, but when I try to use Filter or ALLExcept I am getting errors.
kindly help me with the formula.
Ravi
Solved! Go to Solution.
Hi @Anonymous,
Basically, the filtering functions let you manipulate data context to create dynamic calculations.
For example, if you want to count the number of "Associate Id" which is not "2014", then you can just use the formula below to create a measure for it and show the measure on the report.
Measure = CALCULATE ( DISTINCTCOUNT ( 'Final_Data'[Associate Id] ), FILTER ( 'Final_Data', 'Final_Data'[Associate Id] <> 2014 ) )
Regards
Hi @Anonymous,
In addition, instead of creating a measure or column, you can try creating a new table in this scenario.
Regards
Thanks JJ and v-ljerr-msft , is it possible to have the filter in a formula without creating a new table?
Thanks,
Ravi
Hi @Anonymous,
Basically, the filtering functions let you manipulate data context to create dynamic calculations.
For example, if you want to count the number of "Associate Id" which is not "2014", then you can just use the formula below to create a measure for it and show the measure on the report.
Measure = CALCULATE ( DISTINCTCOUNT ( 'Final_Data'[Associate Id] ), FILTER ( 'Final_Data', 'Final_Data'[Associate Id] <> 2014 ) )
Regards
Thanks v-ljerr-msft, that was very helpful ! but I am still not clear with some of the filter options 😞
I'm trying to figure out how you expect it to work. If you're adding a column to a table, what should that new column look like? Should it copy another column in the same table but remain blank on rows where that first column has a certain value?
Proud to be a Super User!
Hi
Your formula returns a TABLE with of Type "Final_Data" including all records where "Associate ID" is not 2014. There could be many records fitting that filter. In a column of a row only a single value can be stored.
Could apply this filter already when importing the data?
JJ
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |