topic Re: Dynamic measures in DAX Commands and Tips

Dynamic measures

ells69 — Sat, 06 Jul 2024 11:21:29 GMT

Scenario
This data set and report will be shipped to multiple clients. Each client will have their own data, semantic model and report. Each of these will be the same but individual for each client.
The tricky bit is client 1 wants 5 extra measures for KPIs. Client 2 want 7 different measures for KPIs. Client 3 want 3 extra measures for KPIs.

The design for the solution must keep the data set and report the same for all clients. So there is only one version of the solution.
Is there a way of doing this with the measures being stored in the data source?

I want to be able to create one solution for all clients and the customisation of KPIs and perhaps even what is made visible in the reports through a configuration.

Client 1
KPI1 - Sales Target = 110% * last years sales Quantity
KPI2 - Profit Target = 30% * last years sales Quantity

Client 2
KPI1 - Sales Target £= 100% * last years sales £
KPI2 - Profit Target £= 25% * last years sales £
KPI3- Satff Turnover = 10% * Total Staff Quantity

Client 3
KPI1 - Waste Target £= 5% * last years Waste £

My first thought were to store the KPI in a table as below and have say 50 KPIs
KPI ID, KPI Name, KPI DAX, KPI Visible
Then add 50 Measues to the data set
Then when you load the data
make the measure hidden if KPI Visible = FALSE for the measure
Get the DAX for the measure from KPI DAX

Can you do this - create measure from a table of data? Is there an alternative
Thanks

Re: Dynamic measures

lbendlin — Sat, 06 Jul 2024 21:15:00 GMT

This sounds a little bit like RLS. What you could do is use measure tables (I can't believe I just said that!!) and Field Parameters. That way you could map which customer can select which measures from the tables for the field parameters.

Re: Dynamic measures

ells69 — Sun, 07 Jul 2024 09:26:55 GMT

RLS will not cover it. RLS will only affect what is there. It eill not allow you to add a new measure and add the DAX required for the measure.

I want Client 1 to be able to show a new measure called KPI 1 that shows the value "A" . This measure is in their configuration. Client 2 will have in their configuration a new measure called KPI 1 that shows the value "B"
I want Client 1 to be able to ammend the difinition for KPI 1 to show the value "C"
All of this to be done without changing the report or semantic model.

I am considering that this maybe a can of worms that I may not wish to open

Thanks

Re: Dynamic measures

Anonymous — Mon, 08 Jul 2024 01:51:02 GMT

Hi, ells69

Thanks for lbendlin help. Currently, the Power BI does not support the ability to assign different Measures to different users and make other users invisible to each other's assigned Measures. If you have a strong need for this feature, you can create multiple pbix files to share with multiple users, but this is not easy to maintain and manage your data. If you don't care about users seeing each other's KPIs, write multiple Measures in the same pbix file.

Translated with DeepL.com.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data in the Power BI Forum

Re: Dynamic measures

ells69 — Mon, 08 Jul 2024 09:11:29 GMT

Thanks,
It is not so much different users but different companies. I want to be able to distribute one product (power bi report, data set ) and allow them to pick their measures and measure definitions.
This means two different companies may have different ways of creating some of those measures.

I am beginning to think this cant be done
Thanks

Re: Dynamic measures

lbendlin — Mon, 08 Jul 2024 14:34:02 GMT

You can likely cover that when implementing proper Power BI Embedding ("for customers", or "app owns data"). There you should be able to inject measures per customer.