Skip to main content
cancel
Showing results for 
Search instead 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

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
Fabcon_Europe_Social_Bogo

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

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors