Data Model - switch relation from inactive to active by condition.

[Anonymous]

Hi All,
I need your help in switch relation from inactive to active by condition.

I have 4 tables.

tabe 1:

table 2:

table 3:

filter:

Data Model:

dashboard:

I need to switch  filter from table 1 to table 2 according filter selection

link: Power BI WorkSpace

Hope for your help

Tks,

Idan

[Greg_Deckler]

USERELATIONSHIP

https://docs.microsoft.com/en-us/dax/userelationship-function-dax

[Anonymous]

Hi Greg,
Tks For your respond.
I saw USERELATIONSHIP Function Before I posted.

I didn't understand how to use it.
1.Where to put it -> table Active or table Inactive
2.This function need Calculate formula -> However I don't have anything to put in

3.Is a Measure or calculated column ?

 If you can, Pls help me to build the formula

Tks A lot 

Regards,
Idan

[Greg_Deckler]

Quick question, why can't you just Append your Table1 and Table2 so that you eliminate the issue?

[Anonymous]

In this case I just simplified my tables.
In reality I've 2 fact tables contain 500K Rows Each and I want to add filter which filter both.

Thank you Greg for your kindness

Idan

[v-cherch-msft]

Hi @Anonymous

You may set the relationships as inactive. Then create the measures with USERELATIONSHIP Function. For example:

Index1 = CALCULATE(SUM(Table1[index]),USERELATIONSHIP(Table1[index],'filter'[index]))

Index2 = CALCULATE(SUM(Table2[index]),USERELATIONSHIP('filter'[index],Table2[index]))

Attached the sample file for your reference. Below are some articles for you. Please understand that this link is provided “AS IS” with no warranties or guarantees of content changes, and confers no rights.

https://www.kasperonbi.com/dynamic-data-comparisons-using-disconnected-slicers-treatas-and-inactive-relationships/

http://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

Regards,

Cherie

[Anonymous]

Hi Cherie,
Tks for your quick respond 🙂

The solution not solved all the issue.
When I am choosing filter category from filter Table then Table 3 not respond.

Idan

[v-cherch-msft]

Hi @Anonymous

You may use the same way as below: 1. Add relationship between filter and table3  2.Create a measure Score1

Score1 = CALCULATE(SUM(Table3[score]),USERELATIONSHIP('filter'[category],Table3[category]))

Regards,

Cherie

[Anonymous]

Hi Cherie,
I tried to apply this method on real data and it doesn't work.

I upload my real data file contain only relevant information.

Pls take as show in real data sheet - link

Hope for your respond.

Tks,
Idan

[v-cherch-msft]

Hi @Anonymous

I may not have access to view it. Could you provide the .pbix file? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Regards,

Cherie

[v-cherch-msft]

Hi @Anonymous

It seems you may use the visual level filter.

Regards,

Cherie

[Anonymous]

we still have one table missing through filter selection - link

[v-cherch-msft]

Hi @Anonymous

Which table is missing ? If you use userelationship fuction, please use the measure in the table visual or visual level filter.

Regards,

Cherie

[Anonymous]

I tried to put the measure in the table visual or visual level filter but it isn't work.

pls see the link

[v-cherch-msft]

Hi @Anonymous

I have no access to view it. Please check the updated file and let me know if it matches your request. If not, please let me know which table is not correct.

Regards,

Cherie

[Anonymous]

Hi Cherie,
I can't aggregate the data  - attach: Real Data Part 2 Sheet
I tried count default option, calculated column, measure - not work

If you can't open the attached so pls

focus on Table : score summary table by license

create column with random number 1-10
then, try to count license by this column. example:

Rand column  |    count of license column

     1                                 100 

     2                                 199

     3                                 39

     .

     .

     .

    9                                 10

   10                                20

hope for your respond

Idan

[v-cherch-msft]

Hi @Anonymous

You may change the filter to 'is not blank' if the count number is not 1.

Regards,

Cherie

[Anonymous]

Hi Cherie ,
I tried .
not working

attach - link

Idan

[Anonymous]

Because security issues I can't share it on One Drive or Drop Box.
I Sent link from Gmail Drive.

Why can't you see the file which link for power bi service ? (as I sent before )

Idan

[Anonymous]

Pls Try it - link

idan