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 I

## Shows rows only if two columns' values are greater than specific value

Hi, there

I'm new in PBI, here is my question:
I have ID as rows and Week as columns, and I use the measure named TotalCount to calculate the times that every ID shows up in different week as below:

 ID Week1 Week2 001 4 6 002 2 4 003 4 5

Now I want to use a slicer or any other way to filter only when Week1 and Week2's values are Both greater than 3 the rows shows up:

 ID Week1 Week2 001 4 6 003 4 5

How can I achieve my goal?
I can use DAX only.

1 ACCEPTED SOLUTION
Super User

create a measure

Measure = if(CALCULATE(DISTINCTCOUNT('Table'[WEEK]),all('Table'))=CALCULATE(DISTINCTCOUNT('Table'[WEEK]),all('Table'),'Table'[ID]=max('Table'[ID])),1,0)

and add this measure to filter and set to 1

Proud to be a Super User!

16 REPLIES 16
Super User

you can try to create a measure

Measure 2 = if( CALCULATE(sum('Table'[value]),all('Table'),'Table'[ID]=max('Table'[ID])&&'Table'[week]="week1")>3&&CALCULATE(sum('Table'[value]),all('Table'),'Table'[ID]=max('Table'[ID])&&'Table'[week]="week2")>3,1,0)

add this measure to filter and set to 1

Proud to be a Super User!

Helper I

Hi, @ryan_mayu @bhanu_gautam

Let me redefine my problem.

Originally, I have a table which contains column ID WEEK as below:

 ID WEEK other values 001 W427 002 W428 003 W427

And I wrote a measure to calculate the times that every ID shows in different week, which is named TotalCount.
So I use matrix visualization, Row: ID, Columns: WEEK, Value: TotalCount
(I didn't and not allowed to create a new table to pivot/unpivot it)

Here is my question, how can I add an filter or other ways when I use vitualization to show the rows that exactly following my rule.

Super User

have you tried my solution?

I used the similar sample data. However, the week num is fixed, because I wrote week1 and week 2 in the DAX

Proud to be a Super User!

Helper I

I'm kind of confused that which column I should use to replace 'Table'[value] because I use measure as value.

Super User

value is the value column in your table. We need to do the recalculation, so we need that column again.

Proud to be a Super User!

Helper I

Hi

Thank you for being patienty, really appreciate it.
But if the value I need to count is wrote in measure
like the values are come from the measure : TotalCount = count(table[ID])
Should I replace the 'Table'[value] by 'table'[ID] directly?
I tried to do it this way but it shows Can't display the visual.

Super User

pls provide some sample data (not the matrix), and the expected output based on the sample data. Then we can provide you the proper solution.

Proud to be a Super User!

Helper I

Table A:

 ID WEEK 001 W427 002 W428 003 W427 001 W428 002 W428

Expected output:

 ID W427 W428 Total 001 1 1 002 2 003 1
Super User

i am a little bit confused. Based on the sample data you provided, we even don't need to create measures.

Proud to be a Super User!

Helper I

OMG that's true...
so if I want to use this output, and then make some filter,
for example:
show the row only if both of the columns has values
or
show the row only if both of the coumns' values are greater than 1
Should I use the measure that you provide earlier? If so, how?

Super User

create a measure

Measure = if(CALCULATE(DISTINCTCOUNT('Table'[WEEK]),all('Table'))=CALCULATE(DISTINCTCOUNT('Table'[WEEK]),all('Table'),'Table'[ID]=max('Table'[ID])),1,0)

and add this measure to filter and set to 1

Proud to be a Super User!

Helper I

It's exactly what I want! Thank you so much!!

Super User

you are welcome

Proud to be a Super User!

Super User

@syuu , You can create a calculated column that checks if both Week1 and Week2 values are greater than 3.

ShowRow = IF([Week1] > 3 && [Week2] > 3, TRUE, FALSE)

Use this calculated column in slicer

 Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!

Helper I

Hi!

But my problem is that my matrix is only in visualization, not as a table.
Is is possible to try the solution that you shared?
Thank you!!

Super User

Are the coloumns coming from different table

 Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!

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.