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...
  • 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!