How to Assign set of text value to one variable name and filtering them

[Naveen_SV]

HI All,

I am looking for a solution in dax to assign set of value to a variable like

Animals = "Cat","Dog","Elephant"

Plant = "Sherb","Herb", "Moss"

Can anyone please help me with this.

[v-lid-msft · Accepted solution]

Hi @Naveen_SV ,

Please try to change the FilterTable in formula to your new table name

IsInFilters =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        'Table'[Column1] IN FILTERS ( 'FilterTable'[Column1] )
            || 'Table'[Column2] IN FILTERS ( 'FilterTable'[Column1] )
    )
)

to

IsInFilters =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        'Table'[Column1] IN FILTERS ( 'Table 2'[Column1] )
            || 'Table'[Column2] IN FILTERS ( 'Table 2'[Column1] )
    )
)

Best regards,

[v-lid-msft]

Hi @Naveen_SV ,

We can use {"Value1", "Value2", .... , "Value10"} to include  the constant values into a var

ConstantVar = var t = {"Cat","Dog","Elephant"}
return CONCATENATEX(t,[Value]," & ")

Or we can use SelectedColumn to get them from a table

DynamicVar = var t = SELECTCOLUMNS('Table',"Value",[Name])
return CONCATENATEX(t,[Value]," & ")

 
If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.

Best regards,

[Naveen_SV]

HI @v-lid-msft 

Below is the screen shot i am looking at where i need to select "without car" and "with car" in a single filter if i select without car it should select "In progress", "Assigned" and "Closed" and if is elect with Car it should select only "Assigned " and "Closed"

 

[v-lid-msft]

Hi @Naveen_SV ,

First of all, we can create a spreate calculate table as filter.

FilterTable = DISTINCT(UNION(DISTINCT('Table'[Column1]),DISTINCT('Table'[Column2])))

Then we can use a measure in visual filters to meet your requirement 

IsInFilters = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Column1] in FILTERS('FilterTable'[Column1]) || 'Table'[Column2] in FILTERS('FilterTable'[Column1])))

Best regards,

[Naveen_SV]

HI @v-lid-msft 

Thanks for your reply

how did you create this Table when i am recreating it i am unable to write that formuale.

[v-lid-msft]

Hi @Naveen_SV ,

Please try to create a calculated table for "Filter Table" and a measure for "IsInFilters".

Best regards,

[Naveen_SV]

Hi @v-lid-msft 

This is something unforutnate i am unable to recreate your logic when i tried recreating a new table in your PBi file

with the same formulae in the column it's not gettting filtered. 

[Naveen_SV]

hi @v-lid-msft 

[v-lid-msft]

Hi @Naveen_SV ,

Based on the sample table, the without car should have three status, have you tried to filter by with car?

Best regards,

[Naveen_SV]

Hi @v-lid-msft 

Thanks for your responcse

Please try creating it in your PBI file it's shows the same issue.

[v-lid-msft · Accepted solution]

Hi @Naveen_SV ,

Please try to change the FilterTable in formula to your new table name

IsInFilters =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        'Table'[Column1] IN FILTERS ( 'FilterTable'[Column1] )
            || 'Table'[Column2] IN FILTERS ( 'FilterTable'[Column1] )
    )
)

to

IsInFilters =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        'Table'[Column1] IN FILTERS ( 'Table 2'[Column1] )
            || 'Table'[Column2] IN FILTERS ( 'Table 2'[Column1] )
    )
)

Best regards,

[Naveen_SV]

Hi @v-lid-msft 

Thanks a lot its working now.

if i want to hide blank in that how can i do that.

[v-lid-msft · Accepted solution]

Hi @Naveen_SV ,

We can try to generate the filtertable as following:

FilterTable =
FILTER (
    DISTINCT (
        UNION ( DISTINCT ( 'Table'[Column1] ), DISTINCT ( 'Table'[Column2] ) )
    ),
    [Column1] <> BLANK ()
        && [Column1] <> ""
)

Best regards,

[Jimmy801]

Hello @Naveen_SV 

you need to create a new measure, not add a new column

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy