Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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!

Need Help
Tutorial Requests
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    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

16 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

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

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

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

      • ryan_mayu's avatar
        ryan_mayu
        Icon for Super User rankSuper User
        2 years ago

        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