Unrelated measure (parameter) causes full expand of dimension members - how to solve?

[elidman]

Hi all,

I have a more complicated scenario that I have recreated using the simplified example below.

Setup:

• One dimension table with 4 colors (table 1 below)
• One fact table, only containing a values for Red (R) 10 and Yellow (Y) 10 (table 2 below)
• The above two tables have a relationship on ColorKey
• A totally separate 'measure table' with one single measure, and only one row, containing a fixed "variable" Pi = 3.145. This is a calculated measure (table 3 below)
• I can not use Calculated Columns, because in reallity the Pi will depend on the user running the report

The problem

• In a table, when showing only data from DimColor and FactColor Value, I get the members Red and Yellow that have data (table 4 above). Perfect - works great.
• But - when I add the fixed parameter Pi from the table without any kind of relationship - it expands all colors from the Dimension-table (table 5 above)

I would like to get the result of table 4 above, i.e. 2 records, but also with the Pi colum.

Is that possible?

My guess is that some kind of cross-join or auto-exists causes this behavior.

Is there any way to solve this? Something like measure Pi = Calculate(MAX (3.1415), ...TREATASFIXEDVALUE)... or similar?

Any input would be appricated

/Erik

[daXtreme · Accepted solution]

If there are no relationships, PBI creates a cross join between the attributes. You can counteract this by creating a measure that will return non-blank for the rows you want to see and blank otherwise. Then you just filter the visual by that measure (using Filter Pane). The condition should be "is not blank."