Forum Discussion

bapt69's avatar
bapt69
Icon for Helper I rankHelper I
3 years ago

Multiple relationships - Table filtered with inactive relation

Hey,

I got one problem with the relationships...

 

I found this post but it's not solved :

https://community.powerbi.com/t5/Desktop/Multiple-Links-relationships/m-p/2372992#M853396

 

One table "ETABLISSEMENT" related to "VISITES" (active relation) and to "CUSTOMERS" (inactive relation)

With "VISITE", this is the "Etablissement" where the customer were to play

With "CUSTOMERS", this is the "Etablissement" where the customer were been registered

We want the ratio nb visite/registration -> this is good. I get it :

 

 

Taux_fidelisation_etablissement = DIVIDE(
    CALCULATE(
        DISTINCTCOUNT(dmt_visite[client_id]),         
        USERELATIONSHIP(dmt_client[etablissement_creation_id],d_etablissement[etablissement_id]),
            USERELATIONSHIP(dmt_client[client_id],dmt_visite[client_id]),
            dmt_visite[visite_active]=1,
            dmt_visite[Fidelisation]=1),
calculate(count(dmt_client[date_adhesion_club]),dmt_client[Adhérent]="Adhérent",USERELATIONSHIP(dmt_client[etablissement_creation_id],d_etablissement[etablissement_id])))

 

Anyway, to check it out, I would like to visualize the datas :

The problem :

When i put in a "Etablissement" in the right table, I only see the customers who visited this etablissement, not all the visits.

Summary : The first table (on the very left) have to be filtered by the active relation and the center table have to be filtered by the inactive relation.

To be clearer :

For only one customer, it's ok

When I click on a "Etablissements" (table on the very right), it's not ok anymore lol (I would like to see the same lines to calculate the 100%)

2 Replies

    • bapt69's avatar
      bapt69
      Icon for Helper I rankHelper I

      Sorry,

      I did a second "Etablissement" table. Only for this case/visual.

      So, it's working but it's a workaround. I wondered if there is a standard function...

       

      There is a table with the customers and registrationDate and RegistrationCity,

      a table with the visit (venue in the city) with VenueDate and VenueCity.

      And I did a ratio with all the venue (any city) divided by the nb of registration by city.

       

      And I would like 2 tables : one with the registrations and one with the venues (any cities but filtered by the registration city)