Forum Discussion

sara_sameer's avatar
sara_sameer
Frequent Visitor
1 year ago
Solved

Flexible Calculated Table

I have create a table that the data in it takes it from another table which means that its a calculated table, I don't know if its possible or no, but there's a field in the dax of this calculated ta...
Need Help
  • dk_dk's avatar
    dk_dk
    1 year ago

    Hi saraaa sara_sameer (not sure which account is your main)

    Here is a solution using a duplicate table and a measure:


    Here is my sample table of Customers, Services, and the Date on which they used them. (ServiceUsers)

    I duplicate this query and call the table "OtherServices", and load the two identical tables into Power BI.

     

    There should be no relationships between the two tables.

     

    Then you can create a measure:

    Other Services = 

VAR Cust = SELECTEDVALUE(OtherServices[Customer])

RETURN
IF(Cust IN VALUES(ServiceUsers[Customer]),1,0)

     

    And set up your report page as follows:

     

    One slicer, using the Service column from ServiceUsers

     

    One table, showing Customer (and Date) from ServiceUsers. This will show you the list of customers that use the selected service in the slicer.

     

    Another table with Customer, Service, Date columns from OtherServices

     

    You can set a visual level filter for this second table, where you add the Other Services measure, and set it to "Show items when value is 1". This table will now show the customers that use the service you selected in the slicer, AND all the other services (and dates) they use as well. You can adjust the slicer selection to affect what is displayed.

     

    See some examples based on the above sample data:

     

     

     

    I hope this helps! Let me know if you have any questions!

     

     

     

saraaa's avatar
saraaa
New Member
1 year ago

Hello! thank you for your reply. Well I have data of customers and the services they are using.
I wanted to filter upon a specific service and have the unique customers who used this service, after that i needed to see these same customers what other services are they using and when.
The only possible thing was that to create a calculated table to store the id of these customers but i had to write the service name in the dax as well, then, connect the id with the actual data so I can see the other services.
I wanted something flexible to switch around between services
If  you have better idea for this scenario I'd be thankfull if you can share it with me!

dk_dk's avatar
dk_dk
Super User
1 year ago

Hi saraaa sara_sameer (not sure which account is your main)

Here is a solution using a duplicate table and a measure:


Here is my sample table of Customers, Services, and the Date on which they used them. (ServiceUsers)

I duplicate this query and call the table "OtherServices", and load the two identical tables into Power BI.

 

There should be no relationships between the two tables.

 

Then you can create a measure:

Other Services = 

VAR Cust = SELECTEDVALUE(OtherServices[Customer])

RETURN
IF(Cust IN VALUES(ServiceUsers[Customer]),1,0)

 

And set up your report page as follows:

 

One slicer, using the Service column from ServiceUsers

 

One table, showing Customer (and Date) from ServiceUsers. This will show you the list of customers that use the selected service in the slicer.

 

Another table with Customer, Service, Date columns from OtherServices

 

You can set a visual level filter for this second table, where you add the Other Services measure, and set it to "Show items when value is 1". This table will now show the customers that use the service you selected in the slicer, AND all the other services (and dates) they use as well. You can adjust the slicer selection to affect what is displayed.

 

See some examples based on the above sample data:

 

 

 

I hope this helps! Let me know if you have any questions!