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

[Anonymous]

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.
Any answer will be helpful! Thanks a lot!

[ryan_mayu]

@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

[Anonymous]

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:

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.

Any answer will be really helpful! Thanks again!!

[ryan_mayu]

@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

[Anonymous]

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

[ryan_mayu]

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

[Anonymous]

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.

[ryan_mayu]

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.

[Anonymous]

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

[ryan_mayu]

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

[Anonymous]

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?

[ryan_mayu · 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

[Anonymous]

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

[ryan_mayu]

you are welcome

[bhanu_gautam]

@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

[Anonymous]

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!!

[bhanu_gautam]

Are the coloumns coming from different table