Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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!
Solved! Go to Solution.
create a measure
Proud to be a Super User!
@Anonymous
you can try to create a measure
Proud to be a Super User!
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!!
@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
Proud to be a Super User!
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.
Proud to be a Super User!
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.
Proud to be a Super User!
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 |
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!
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
Proud to be a Super User!
It's exactly what I want! Thank you so much!!
you are welcome
Proud to be a 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
Proud to be a Super User! |
|
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
Proud to be a Super User! |
|
User | Count |
---|---|
85 | |
81 | |
64 | |
53 | |
45 |
User | Count |
---|---|
100 | |
48 | |
41 | |
39 | |
38 |