DAX formula in a measure to return a ~"Global Config Value" in a single row table?

[TeeGee]

I have a single row "Global Variables table that I'm storing various configurable "weighting" variables; I want to create a measure for each column (variable) in this table.

Currently, I am using:

Biz Continuity IMPACT Score Weight = FIRSTNONBLANK('Global'[BusContinuity Impact Weight],1) 

Is this the correct way to do it?  It seemed to be working generally fine, but then if I include that measure in a table visual that is filtered down to one row via a slicer slection, I see some strange behavior where the number of rows in the table visual increases.

So I guess my questions are generally:

a) Is that formula the best way to do this?

b) Any ideas what might be causing this unexpected (to me) behavior?

[Nathaniel_C]

Hi @TeeGee ,

FIRSTNONBLANK() has some interesting behaviors. You may be aware of them, but it is all about the sort order. So instead of the 1, you used a date field argument, or if you add an index column, and refer to either of those columns, you will be sure of your sort order. If you leave it to the 1, it will sort that column from top to bottom, or bottom to top - anyway - it may not sort in the order that your column is currently. 

I did one yesterday that you might want to look at, but you may have to have your browser translate, because the posting notes are in Spanish. 

If you are able to provide more info or a file, we may be able to dig further into this. You might also google @MattAllington  blog on this very subject.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[TeeGee]

Ok, I've reproduced the undesireable behavior I was seeing before, I'll do my best to describe this.

See screenshot of report below.

I have a disconnected (no relationships) "Global Variables" table, that I am using to store multiple variable "weightings".

I've written a measure to lookup the weight for each relevant variable, like so:

RPO Weight = FIRSTNONBLANK('Global'[RPO Weight],1)

I've also tried these:

RPO Weight = CALCULATE(FIRSTNONBLANK('Global'[RPO Weight],1),ALLSELECTED('CBF Analysis'))

RPO Weight = CALCULATE(FIRSTNONBLANK('Global'[RPO Weight],1),ALLSELECTED('CBF Analysis'),ALLSELECTED('CBF Analysis'[Company]))

The FACT table is 'CBF Analysis'

The DIM table is Company.  There is an active relationship between Company and 'CBF Analysis'

a) The upper left slicer is based on 'Company'[Company]

b) The one below that is on 'CBF Analysis'[Company]

c) The one below that is on 'CBF Analysis'[CBF ID]

Now here's the strange part...

Both slicers (b) and (c) (derived from the FACT table itself) work as expected - when filtering on one [CBF ID] and one [Company], the table visual filters to one row.  Perfect.

However, when using slicer (a), which is on the DIM table 'Company', it is successfully performing the filter on the FACT table, but the [RPO Weight] measure is not respecting that filter;  so, the two columns/cells from the FACT table are null, but the [RPO Weight] cell is returning the weight, forcing a mostly null row to be displayed.

If I remove [RPO Weight] from the table visual, then that slicer behaves as expected, and the visual shows one row as expected, instead of two.

For now, I suppose I can just base all my slicers on FACT tables, but that seems not just philosophically problematic, but I can foresee many non-trivial issues arising because of this.

Is there a way to make this work???   Is there some better approach I can take for implementing "Global Variables" throughout this app without banging my head against this problem everywhere?

Maybe, is there some way in the measure to inherit the row filter context when it is called, and maybe do something like a countrow on the FACT table using that context, and if it is 0 return BLANK?

Or if I was to do a calculated column in the FACT table, which I'd somewhat rather not do for no particularly strong reason, but under the circumstances if it worked I'd be satisfied with that.

EDIT:  Ok, adding a calculated column works, so at least I have this as an option.  My problem now is that I have about 40+ existing measures already written that I'd have to completely redo now....but I'd really rather avoid that if possible, and I'd also like to really know why this is happening, or if there is a way to do it without going that route (like the countrows with row context above).

EDIT 2: I just realized something else that might provide a hint.  In the table visual below:

[RPO Score] is actually coming from a lookup table (a textual value is stored in the FACT table)

[RPO Criticality Score] = [RPO Score] x [RPO Weight] 

So it looks like Power BI is realizing that when a value is selected in Slicer (a), it doesn't bother going and fetching that value, resulting in the two null cells in the row.  However, the [RPO Weight] measure not respecting the slicer seems to be forcing the row to be displayed, and that's why the first 3 columns from the FACT table are populated.

[TeeGee · Accepted solution]

Finally figured it out, the solution is embarrassingly simple:

RPO Weight = IF(ISBLANK([RPO Score]),BLANK(), FIRSTNONBLANK('Global'[RPO Weight],1))

Although, I have a feeling this approach isn't perfect, I think I may have gotten a bit lucky in this case so this problem could still arise in a different scenario....had I not been looking up the [RPO Score] in a related table, the lookup of which failed due to the slicer filter, therefore returning a BLANK for the looked up value, what could I have checked for ISBLANK() in my measure?

If anyone sees a less vulnerable approach to this, please chime in!!