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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Anonymous
Not applicable

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:

IDWeek1Week2
00146
00224
00345


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:

IDWeek1Week2
00146
00345
   


How can I achieve my goal?
I can use DAX only.
Any answer will be helpful! Thanks a lot!

1 ACCEPTED SOLUTION

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
11.PNG




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

16 REPLIES 16
ryan_mayu
Super User
Super User

@Anonymous  

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
11.PNG




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Hi, @ryan_mayu @bhanu_gautam 

Thank you for both of your reply.
Let me redefine my problem.

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

IDWEEKother values
001W427 
002W428 
003W427 


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.

Any answer will be really helpful! Thanks again!!

@Anonymous 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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.

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Table A:

IDWEEK
001W427
002W428
003W427
001W428
002W428


Expected output:

IDW427W428Total
00111 
002 2 
0031  

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

 

11.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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?

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
11.PNG




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




bhanu_gautam
Super User
Super User

@Anonymous , 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!




LinkedIn






Anonymous
Not applicable

Hi!

Thank you for your reply.
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!!

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!




LinkedIn






Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

May 2025 Monthly Update

Fabric Community Update - May 2025

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