Issue in applying filter in Power BI Desktop DAX forumla

Question

I am facing an issue while working with power bi desktop. i am having an csv file as a datasource and i am having a column with multiple values, say "abc, def", "jkl" "zyz" etc.

Here, i need to generate a report with rows having showing "def" & "jkl" . How to filter this using DAX Filter command. i wanted to fetch, filter only two values CTF& EVE in the Power BI report. I tried with creating a calculated column and applied the below code but it didnt work:

Columnjkl = FILTER((Table1,OR(Table1[mycolumn1] == "def" || "jkl"))

filter-2cols-ctf-eve-

Have you tried creating a report? Can you attach a screenshot? This looks like a simple Filtering question. You probably won't even need DAX — Gangula
Right. You should be able to use a slicer or a visual/page/report-level filter instead of writing the values in the DAX. — Alexis Olson

OscarLar OscarLar · Accepted Answer · 2019-07-23T12:14:38

You need to read how DAX syntax is written, check this page to see how you use the FILTER function (FILTER) and this for how to use the OR function (OR)

Basically, Filter returns a table, so using it to calculate a column will not work. However, by implementing the FILTER function inside a CALCULATE you can change/modify what the main argument of CALCULATE will evaluate. The calculation flow in DAX is such that first the filter argument is evaluated and then the aggregation (or what ever) is evaluated. Example:

NumberOfRowsWithABC = 
CALCULATE = 
    COUNTROWS('Table1'),
    FILTER(
        'Table1',
        'table1[mycolumn1] = "abc"
    )
)

First the FILTER function only selects the rows in 'Table1' where [mycolumn1] has the text value abc. Then it passes this reduced table to the COUNTROW function, which counts the number of rows in the table passed to it.

The syntax for the OR function is wrong. This is how you should write it:

OR(
    'Table1'[mycolumn1] = "def",
    'Table1'[mycolumn1] = "jkl"
)

Perhaps a better way of writing this OR function is using the in-command as the filter argument of the calculate function:

NumberOfRowsWith_def_and_jkl =
CALCULATE(
    COUNTROWS('Table1'),
    'Table1'[mycolumn1] in {"def", "jkl"}
)

But as Gangula wrote, you don't need to filter 'Table1' using the FILTER function, it can all be done visually on the dashboard.

Issue in applying filter in Power BI Desktop DAX forumla

1 Answers