Problem with Applying Slicer/Filter

[AZL]

Hello, 

Guys I have the follwoing problems which I hope you will help me solving it.

I want to create a page slicer/filter for Rep. to compare how many products each rep. is handling based on current and new plan.Thus, I want to create two tables on the same page with a slice..The first table will represent the current plane and the other table will represent the new plan....something like this:

As you can noticed Rep. "D" is not available in current plan...my question is how can I create one slice/filter with all rep names that will filter both tables correctly.for example in case I select Rep. "D",the current plan table will show nothing while the new plan table will show D = 2 products.

Thanks in advance.

[parry2k · Accepted solution]

@AZL solution attached, everything is done in edit query.

[v-lili6-msft · Accepted solution]

hi, @AZL 

You could get it by another way:

Step1:

Create a dim Rep table for slicer.

Step2:

Create two relationship between dim rep table and basic table by Current rep and New rep column(These two relationship must be inactive)

Step3:

Use USERELATIONSHIP Function to create two measure

Current Plan = CALCULATE(COUNTA('Table'[Product]),USERELATIONSHIP('Dim Rep'[Rep],'Table'[Current]))+0

New Plan = CALCULATE(COUNTA('Table'[Product]),USERELATIONSHIP('Dim Rep'[Rep],'Table'[New]))+0

Step4:

Now, drag Rep field from dim table and two measure into a table visual, and use Rep field from dim table for slicer/filter

and here is sample pbix, please try it.

Best Regards,

Lin

[parry2k · Accepted solution]

@AZL solution attached, everything is done in edit query.