cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Power BI Dax Filtering Question - Selecting all current and lower index values

i have the below table called "Skill Selection List":

Skill Level
 Writing 1 Writing 2 Writing 3 Writing 4 Reading 1 Reading 2 Reading 3 Reading 4 Listening 1 Listening 2 Listening 3 Listening 4 Speaking 1 Speaking 2 Speaking 3 Speaking 4

i have created a slicer with the combination of the two columns from the "Skill Selection List" Table. i want to create a measure that returns a dynamic table , where if i select, for example, Speaking 3 and Listening 2, the measure will return a table of Speaking 1, 2, 3 and Listening 1, Listening 2

1 ACCEPTED SOLUTION
Regular Visitor

HI All,

Apologies if my request was confusing!
The measure i have requested a solution for is a variable in a larger measure, which eventually gets wrapped in COUNTROW

If anyone cares, this is the solution i ended up going with

VAR filtertable =
SELECTCOLUMNS( GENERATE
'Skills Selection List',
GENERATESERIES(1,''Skills Selection List',[Level],1))
"Skill",[Skill},
"Level",[Value]
)

7 REPLIES 7
Community Support

Many thanks to lbendlin  and ArmandoFranco  for their prompt reply.

Can you tell me if your problem is solved? If yes, please accept it as solution!

Regards,

Nono Chen

Frequent Visitor

You could add a column with this formula.

``````All Skills =
VAR _Skill = 'Skill Selection List'[Skill]
VAR _Level = 'Skill Selection List'[Level]
RETURN
CONCATENATEX (
FILTER (
ALL ( 'Skill Selection List' ),
AND (
'Skill Selection List'[Skill] = _Skill,
'Skill Selection List'[Level] <= _Level
)
),
'Skill Selection List'[Skill] & " " & 'Skill Selection List'[Level],
", "
)``````

Regular Visitor

I was hoping to get something similar to this but in a measure form, and the results to be something like this, as i wanted to later on use this virtual table in a UNION measure:

 Speaking 1 Speaking 2 Speaking 3
 Listening 1 Listening 2

i would appreciate any help!

Frequent Visitor

As others have said, a measure can not return a table.

That's why I used CONCATENATEX to get a single value.

Super User

Not possible. You must consume the virtual table inside the measure. Measures can only return scalars.

Regular Visitor

HI All,

Apologies if my request was confusing!
The measure i have requested a solution for is a variable in a larger measure, which eventually gets wrapped in COUNTROW

If anyone cares, this is the solution i ended up going with

VAR filtertable =
SELECTCOLUMNS( GENERATE
'Skills Selection List',
GENERATESERIES(1,''Skills Selection List',[Level],1))
"Skill",[Skill},
"Level",[Value]
)

Super User

Measures can only return scalar values. Are you sure your UX design will not be confusing to users?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors