Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to filter data based on two columns

Hi,

 

I am new to power bi platform, can someone help me.

 

I want to filter my table/data in my visuals based two columns. 

Table

for example i have a table with columns as transaction, period, code , Employee, amount, quantity, nature. 

And now in my table visual i want see results based on period and Nature column,

Data should filter if corresponding values for P1 has both 'A' and 'B' then it should return values else nothing. it shuould not pick other periods data. 

I expect to see only P1 results. P2 should not seen in any visuals because it does not have 'A' value it just had 'B'. 

Result

Result of visual table as below

 

Need Help
Show and Tell
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    Anonymous 

    here is a workaround.

    Column = IF(CALCULATE(DISTINCTCOUNT('Table (2)'[NATURE]),ALLEXCEPT('Table (2)','Table (2)'[period]))=1,"no","Yes")

    then add a filter and select Yes

6 Replies

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

    Anonymous 

    here is a workaround.

    Column = IF(CALCULATE(DISTINCTCOUNT('Table (2)'[NATURE]),ALLEXCEPT('Table (2)','Table (2)'[period]))=1,"no","Yes")

    then add a filter and select Yes

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hey ryan_mayu , it worked, Thank you so much ğŸ˜Š. Do you mind explaining the formula, i am not understanding how to start read, is it from inner to outer or outer to inner function?

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

        Anonymous 

        it's counting the nature values for each period. If only one value, return no, otherwise retun yes.

  • ChaitanyaTagare's avatar
    ChaitanyaTagare
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    You can create two slicers on top of the table.

    1.Drag slicers controll from visualization section.

    2. Slicer1- Period  (drag period field into this slicer)

    3. Slicer2- Nature (drag Nature field into this slicer)

    When you select P1 period from slicer1 it will show nature A and B

    and for P2 it should show values for nature B only.

     

    With slicer2 you will able to select Nature A or B and corrosponding period.

    Let me know if this helps.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Try this type of measure for all measures like quantity, amount etc

     


    new Quantity =
    var _1 = calculate(sum(Table[Quantity], filter(allselected(Table), Table[code] = max(Table[code]) && Table[period] = max(Table[period]) && Table[Nature] = "A"))
    var _2 = calculate(sum(Table[Quantity], filter(allselected(Table), Table[code] = max(Table[code]) && Table[period] = max(Table[period]) && Table[Nature] = "B"))
    return
    calculate(sum(Table[Quantity]), filter(Table, not(isblank(_1)) && not(isblank(_2))))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hey amitchandak , Thanks for replying, i didn't understood why code column was selected, i need only period and nature to be used.

       

      Can you please explain 

       

      Thanks,

      Shravz