storing and using measure names from a table

[ThomasDay]

Hello All,

I am going to make use of the "master slicer" pattern I've seen a bunch.  The trick is I'd also like to store a measure name in a column and "parse" it out to use in a filter.

The master slicer table will be of the form below.

And I will be calculating a percentile to store in a table with a formula something like this with MeasureName coming from the MeasureName column:

25thPercentile = PERCENTILEX.INC(ALLSELECTED([ProviderNo]), [MeasureName], 0.25)

There are hundreds of measure names to choose from...and each will have a different set of filter values.

I need some help getting off the dime here.  Not sure what to do to make the MeasureName dynamic.  

Thanks in advance!
Tom

[Anonymous]

There used to be a measure was to do this with MDX. It was called String to Member.

Here is the idea you can vote for it: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/33095020-programmatically-change-...

[NV]

Hi @ThomasDay, thanks for reaching out to me. If I've understood the question right, what you are trying to do is quite complex, so I can't think of a clean cut solution for solving this.

Here's how I've understood this so far:

For simplicity, lets say you have 1 report page with 3 card vizualizations (25th percentile, 50th percentile, 75th percentile).
You want to use a slicer with 100 + measure names, so that when you select a measure from the slicer, the three cards will reflect each percentile calculated with that measure. Eg. selecting FTEsAcuteCare from the slicer would result in the cards displaying each percentile for FTEsAcuteCare.

Let us know if this interpretation of the problem is close enough and maybe someone else might have a light bulb moment and step in 🙂

If this is accurate, then for one the measure should result in a vector of values, not a value, because you have to have a vector of values to be able to calculate a percentile. I assume that your measures are set up like this, so one solution would be to use the SWICH statement (as in the parameter table link above) where you check which value has been filtered from the measure table and catch the selected measure with a SWITH statement like:

25thPERCENTILE =
IF (
HASONEVALUE ( Measures[MeasureName] ),
PERCENTILEX.INC(ALLSELECTED([ProviderNo]),
SWITCH (
VALUES ( Measures[MeasureName] ),
"FTEsAcuteCare", [FTEsAcuteCare],
"2ndMeasure", [2ndMeasure],
...
)
, 0.25)
...

This is not dynamic as the measures have to be hardcoded into the dax-statement, but with good documentation and a mainenance plan for updating the dax-statements, this might be a simple enough approach.

Of course there could be a more dynamic solution to this, but let me know if this brings you any closer to a solution that would suit the requirements. 

[ThomasDay]

Hello all--and thanks for helping. 

• We know that we cannot programmatically change a DAX measure based on a string value in an expression.
• On the other hand, 20mm rows, computing 600 measures for 5000+ entities and building synthesis ranking in this model--even if I could programmatically change a DAX measure in an expression--is a really good ETL stage for a large data set.
• These data update quarterly...so that allows a little latitude thinking about stages without building in much maintenance/worry/time.
• A new stage can take care of this issue:
  • Export ( a little clunky) from a visualization of all entities and 600 measures (not sure when memory pops--so it may take several sets of the measures).  With luck it's not too many sets...let's say I get lucky here.

  Use SQL to transform into a long thin file like this...there are commands to do this part.

  And voila!  That becomes the input for the next stage.  It's a big file 5000entities*600measures*no of years but now each calc is available!

  I can then add the next layer of value add--compute the various percentile values for relevant cohort groups of providers (just use/share the base ETL provider file for that), build a parameter table for slicers with the measures appearing in the slicers--with a group slicer, then a measure slicer.

  OK, it's early in figuring out the next steps, but this seems quite productive as an approach.

  Thanks again---let me know what you think,

  Tom

  PS: if anyone knows how I can use the "work in excel" feature instead of the visualization export I am envisioning...I'm all ears!

[Greg_Deckler]

Please let me know if this is what you are asking, you want to be able to create measures and have your table of MeasureNames automatically reflect these new measures?

[ThomasDay]

Hello @Greg_Deckler

Hmmm, I don't think that's quite it.  I'm looking to use measure names from a column in a table to create a DAX equation.
So, in my example, MeasureName comes from a column in a table--stored as text and when I select a row in that table, I would like to insert the MeasureName into a DAX equation.

Is that clearer?

Tom

It's really like putting a measure name in a slicer so you pick a measure and the filters that you want to see as a user....

[Sean]

This sounds like a parameter table!

http://www.daxpatterns.com/parameter-table/

[ThomasDay]

Yes...yes it does.  My head almost explodes daily with all the new things to figure.  I can't thank you enough for pointing the direction (as always).  I'll try some simple examples to try it out.

Tom

[ThomasDay]

Inserting a MEASURE Name into a DAX expression remains unsolved.  The Parameter Table inserts values into expressions but not MEASURES from what I see.  

Let me go to what I'm trying to do instead.

The idea is to have a list of MEASURES, pick one (something like on the visualization FIELDS list) and show a visualization page for that Measure....

Alternatively, does anyone know how to cycle through an entire or selected measure list and do computations for each one writing one row of calcs for each to a table?

Tom

[Greg_Deckler]

So, you could potentially solve this by publishing your report to the Service. Here is the thought. Create a report page for each measure. Put your measure names in a table. Publish to service. Go to each report page and create a card visualization that filters to the measure name for that page. Pin to dashboard.

In theory, you should get a dashboard of measure names. Clicking the measure name should transport you to the page in the report that the visualization came from.

Not optimal.

[ThomasDay]

It's a possibility...we'll publish 100 measures so it's a lot.  And on each one there are maybe 30-50 different groups of data points to show...so it's very "reference book" like in that sense.