How to filter using two columns at the same time?

[EugenioProlog]

Below is attached my pbix file.
https://drive.google.com/file/d/1VWLANFcNBcHb8OzumVjO2v57_6a1DZdq/view?usp=sharing

I have this table structure, with a fact table for my sales which have two user columns: seller_id and referrer_id. I have one dim table for each of them, but they represent the same employees.

Now here is what i want to do: I want to create a dashboard with one single data segmentation in order to select one employee (employee X), and this data segmentation should filter at same time two cards with the following metrics:
1 - Number of sales in which employee X was a seller
2 - Number of sales in which employee X was a referrer

How would I create my metrics or how should I adapt my dataset in order to achieve that?

thank you!!

[v-tsaipranay]

Hi @EugenioProlog ,

We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
If you still require support, please let us know, we are happy to assist you.

Thank you.

[v-tsaipranay]

Hi @EugenioProlog,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[v-tsaipranay · Accepted solution]

Hi @EugenioProlog ,

Thank you for reaching out to the Microsoft fabric community forum.

After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used the pbix which you provided and successfully implemented it.     

I am also including .pbix file for your better understanding, please have a look into it:
Also thank you @Ashish_Mathur, @TomMartens and @GeraldGEmerick  for your helpful response.

Hope this helps. Please reach out for further assistance.

Thank you.

[v-tsaipranay]

Hi @EugenioProlog ,

I wanted to follow up on our previous suggestions regarding the issue. We would like to hear back from you to ensure we can assist you further.

Thank you.

[GeraldGEmerick]

@v-tsaipranay Looks like you went the disconnected table route. Nice catch on the fact that you needed to use SELECTCOLUMNS, I completely forgot that would be necessary.

[Ashish_Mathur]

Hi,

I'd suggest you you restructure the fact table in power query.  Select all columns other than the user_id column columns, right click and select "Unpivot Other Columns".  Rename the Attribute column to Type.  Now just have 1 dim table and create a Many to One relationship from the Type column of the Fact table to the dim table.  Create the user_name slicer from the dim table.  Write these measures

S = sum('fact_sales'[Price])

Seller = calculate([s],'fact_sales'[Type] = "seller_user_id")

Referrer = calculate([s],'fact_sales'[Type] = "referrer_user_id")

Hope this helps.

[TomMartens]

Hey @EugenioProlog ,

assuming there is one slicer, I assume it's populated by dim referrer, then you can create two measures like so:

measure referrer =
calculate(
sum('fact_sales'[numeric column from fact_sale]
)

and the seller measure

measure seller =
var selectedreferrer = values('dim_referrer'[id])
return
calculate(
sum('fact_sales'[numeric column from fact_sale],
all(dim_referrer'[id]),
'dim_seller[id] in selectedreferrer
)

Hopefully, this helps to tackle your challenge. If not create sample data, and share the pbix, via OneDrive, Google Drive, or Dropbox.

Regards,

Tom

[EugenioProlog]

hi @TomMartens I put the link to the pbix file on the original post. 

[GeraldGEmerick]

@EugenioProlog I would think that you could create a dim table that combines the unique values from dim_seller and dim_referrer. Something along the lines of the following:

dim_sellersreferrers = DISTINCT( UNION( 'dim_sellers'[user_id], 'dim_referrers'[user_id] ) )

Now, probably better to do this in Power Query but, same concept here.

Relate this to your dim_referrers and dim_sellers tables. Now, some relationship in there will need to become inactive, but you can use the USERELATIONSHIP function within CALCULATE to resolve that. The other method would be to not relate the table to either dim table and leave it disconnected. You can then form the relationship in the measure calculation, such as using the IN operator.