Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
hemingt
Helper V
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

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)

Capture4.JPGCapture5.JPG

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.

View solution in original post

10 REPLIES 10
v-juanli-msft
Community Support
Community Support

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?

Capture19.JPG

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.

 

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.

Hi @hemingt 

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

Measure = VALUES('Table'[week])
Capture8.JPG
 
It works when i create a new table
Capture9.JPG
 
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.
Capture10.JPG
 
 
 
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.

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?

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]))

Capture21.JPG

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.

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!

Hi @v-juanli-msft ,

 

Is there any advice?

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)

Capture4.JPGCapture5.JPG

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.

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

Your solution works well!

Hello @v-juanli-msft 

 

Can you have a look? Thank you!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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