How to use the value that the user chose in a slicer to filter a table..

[Syugiku]

Dear all

I have to create a new table from an existing table filtered by the value the user chose.

there are 2 tables. TableA is for a slicer like below.

column1

---------- 

A

B

TableB is the target to filter like below.

column2 column3

--------------------

A     1

B     2

Then I want to create a table by a formula like below.

TableC = filter(TableA,TableB[column2]="the chosen value of slicer")

How can I do this in PowerBI?

[waltheed]

Hi Syugiku,

Can you give this a try:

TableC = filter(TableB, VALUES(TableA[Column1]))

There has to be a relationship between Table A and B on Column1.

[Syugiku]

Hi Mr.Walther

In addition I tried the formula you gave me with TableA-B relationship on Column1.

TableC = filter(TableB, VALUES(TableA[Column1]))

But it didn't work and gave me an error again. PowerBI says "values(TableA[Column1])" has multiple records.

[Syugiku]

Hi Mr.Walther

Thank you a lot but I can't create any relationships on the columns because both the columns on TableA/B to be filtered aren't unique. Is there other way to solve? I'm really stacked and taking many days on this problem during the evaluation of PowerBI.

[ankitpatira]

@Syugiku I think you've done it yourself, you got the formula just need to create new measure by going to Modelling tab in power bi desktop, unless I've misunderstood you ?

[Syugiku]

Thanks for your message.

Sadly I think you've misunderstood and i'm sorry about my bad description.

When I click "B" on the slicer from TableA, I want to get the result on TableC like below:

column2 column3

--------------------

B            2

I tried many ways but I always get an error. I have no more idea about the formula I should put to create TableC automatically and interactively.

TableC = filter(TableB,TableA[column1] = TableB[column2]) --> this doesn't work.

[Eric_Zhang]

---

@Syugiku wrote:

Thanks for your message.

Sadly I think you've misunderstood and i'm sorry about my bad description.

When I click "B" on the slicer from TableA, I want to get the result on TableC like below:

column2 column3

--------------------

B            2

I tried many ways but I always get an error. I have no more idea about the formula I should put to create TableC automatically and interactively.

TableC = filter(TableB,TableA[column1] = TableB[column2]) --> this doesn't work.

---

@Syugiku

May I know why a TableC is required. Regarding the bold part, I think creating a relationship between tableA and tableB would be OK. When clicking a the slicer "B" on the slicer from TableA, TableB would get the expected output.

If TableC is mandatory, isn't TableC=ALL(TableA[column1]) be ok after creating a proper relationship between TableA and TableC?

[Syugiku]

Let me tell you all what I wanna do with PowerBI.

I want to get two sums of order count.one is by new customers and another is by regular customers in any period.

You have one table. it has 3 colums. orderID,memberID,orderdate.orderID must be unique in the table.

At first you decide the period like May 1 2016 - May 10 2016. -- period1

Next you decide one more period before the first one like April 1 2016 - April 30 2016 -- period2.

Both period1 and period2 must be chosen interactively by each dashboard users.

Then the definition of new customers is the customer who ordered in period1 but didn't order ini period2.

The final output I imagine is like below

--------------------------------------------------

date       sum(new)        sum(regular)

May 1    1                      2

May 2    2                      3

May 3    0                      2

[Eric_Zhang]

@Syugiku

Thanks for the clarification, it makes things more clear.
To get the expected output, instead of tableC, I use 2 measures.

sum(new) = IF(ISBLANK(CALCULATE(DISTINCTCOUNT(Table2[memberid]),FILTER(Table2,NOT(CONTAINS(copyTable,copyTable[memberid],Table2[memberid]))))),0,CALCULATE(DISTINCTCOUNT(Table2[memberid]),FILTER(Table2,NOT(CONTAINS(copyTable,copyTable[memberid],Table2[memberid])))))

sum(regular) = DISTINCTCOUNT(Table2[memberid])-[sum(new)]

I've uploaded a demo pbix for your reference.

If you have any question, feel free to let me know.