How to use VALUES with other filters?

[hemingt]

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!

[v-juanli-msft]

Hi @hemingt 

Values function will return a one-column table.

"Filter(Task, Task[FixVersion]=SELECTEDVALUE(Task[FixVersion]))"

What would you like with this function?

Is the following your wanted?

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.

[hemingt]

Hello @v-juanli-msft 

Thank you very much for your reply.

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.

[v-juanli-msft]

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.

[hemingt]

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?

[v-juanli-msft]

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.

[hemingt]

Hi @v-juanli-msft 

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!

[hemingt]

Hi @v-juanli-msft ,

Is there any advice?

[v-juanli-msft · Accepted solution]

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.

[hemingt]

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

Your solution works well!

[hemingt]

Hello @v-juanli-msft 

Can you have a look? Thank you!