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

Helper V

## How to use VALUES with other filters?

Hello

I have a table named Tasks, and there are several columns, and two columns named 'Week' and 'FixVersion'

I want to firstly filter the table according to the column FixVersion with the condition Filter(Task, Task[FixVersion]=SELECTEDVALUE(Task[FixVersion])), then get the unquine with VALUES(Task[Week])

Do you know how to do? Thank you very much!

1 ACCEPTED SOLUTION
Community Support

Hi @hemingt

Create two measures

```Measure = CALCULATE(MAX(Tasks[Week]),FILTER(ALLEXCEPT(Tasks,Tasks[FixVersion]),[Type]="End"))

Measure 2 = IF(MAX(Weeks[Week])<=[Measure],1,0)```

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
10 REPLIES 10
Community Support

Hi @hemingt

Values function will return a one-column table.

What would you like with this function?

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helper V

Hello @v-juanli-msft

I only want to the unique value of the column week based on your Filter. How can I get it?

if no need to filter FixVersion, it's very simple, just to use VALUS(Task[Week]).

but I also want to apply the filter according to the slice, Task[FixVersion]=SELECTEDVALUE(Task[FixVersion]), and then get the unqiue value of Week.

Community Support

Hi @hemingt

As tested, the measure below can't display correctly in a table visual

`Measure = VALUES('Table'[week])`

It works when i create a new table

But a table can't change with the slicer, to change with slicer, you need to create a measure.
As tested, in this case, when i add "week" column in a table visual, it shows distinct week numbers and change with slicer.

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper V

Hello @v-juanli-msft

Thank you very much!

Here is my pbix file <https://1drv.ms/u/s!AsM9bO8zGUN6qzfNZxmmRlNzWq-O?e=DhTMyQ>

I want the restrict the x-axis value(Week) according to the slice(FixVersion).

in my demo, if FixVersion selected as FMR2001, the max x-axis Week value should be 1917. if FixVersion selected as FMR2002, the max x-axis value Week should be 1915.

if FixVersion not selected or all selected, the x-axis value should be 1917.

Can you help to have a look? Is it possible to solve?

Community Support

Hi @hemingt

I can write a measure with hard code

`Measure = IF(MAX(Tasks[FixVersion])="FMR2001",[Acumulative Count],IF(MAX(Weeks[Week])<=1915,[Acumulative Count]))`

If you don't need this hard code, please tell me the logic of "restrict the x-axis value(Week) according to the slice(FixVersion)".

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper V

I did not want the hardcode.

The logic is I have many tasks belong to different projects. These tasks  will start at certain Week number and end at certain week separately. I want to show such line chart, we can see how many tasks start at each week and end at each week. We also can slice the project to show tasks for each project.

Since the max Week may different for each project(task end week), I want to show x-axis week is the actual max week of the project.

If still something unclear, please let me know. Thank you!

Helper V

Community Support

Hi @hemingt

Create two measures

```Measure = CALCULATE(MAX(Tasks[Week]),FILTER(ALLEXCEPT(Tasks,Tasks[FixVersion]),[Type]="End"))

Measure 2 = IF(MAX(Weeks[Week])<=[Measure],1,0)```

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper V

Thanks a lot for your great support @v-juanli-msft

Helper V

Hello @v-juanli-msft

Can you have a look? Thank you!

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 - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors