Flexible Calculated Table

[sara_sameer]

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 table that should be changed, its not a fixed condition. As far as I know after the report is pulished I cannot edit the calculated table dax for the desired condition so i need to create something like a text box lets say, whenever me or someone else has view access to this report, can enter something or like a text in this text box that will make the dax change.
Is there such thing?

[saraaa]

Sorry for the confusion of account changing! 😅

[audreygerred]

Hi! Calculated tables and calculated columns are computed at the time of processing the model/refresh and are stored in the model and cannot be adjusted or dynamic. Have you tried to solve your issue using measures instead?

[dk_dk]

Hi @sara_sameer 

Calculated tables only refresh / recalculate when the dataset itself is refreshed, in general you cannot use any text input or parameters to modify the calculation or recalculate your table "on the fly".

Depending on your use case and data:

- you might be able to use measures instead of a calculated table to achieve what you want

or

- you would need to build a Power App that is embedded in your PowerBI report, and takes an input, stores it in a database somewhere, and initiates the report refresh for the report itself (and the store input is loaded into your model to influence a calculation). While tecnhically possible, it is quite a workaround with a lot of limitations, so I would encourage you to try the first option instead, or share a bit more about your data and usecase so that we can think of an alternative solution to achieve what you want.

[saraaa]

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 · Accepted solution]

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!