Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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,
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,
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,
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"
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,
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.
Hi @Naveen_SV ,
Please try to create a calculated table for "Filter Table" and a measure for "IsInFilters".
Best regards,
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.
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,
Hi @v-lid-msft
Thanks for your responcse
Please try creating it in your PBI file it's shows the same issue.
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,
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,
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |