How to filter data based on two columns

[Anonymous]

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

[ryan_mayu · Accepted solution]

@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]

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]

@Anonymous 

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

[amitchandak]

@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]

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

[ChaitanyaTagare]

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.