Getting selected value down in the table with no relation is not supported - how to workaround?

[MikkoL]

Hello,

I have a simple problem with no solution😅 I have build Profit and Loss with Power BI and now I want to have a selection to show Group level versus Company level figures. In the Group level figures we filter inter company transactions away, whereas in Company level there is no filtering.

The idea was to be able to select Company or Group level in a slicer and do the filtering on the table. As I have read you cannot propagate the slicer selection (Group/Company) down to the transaction table (the SELECTEDVALUE will return BLANK  on table level).

Is there any workaround how to get this to work (now I filter explicitely inter company transactions away on group level and select all transaction on company level...). Thank you for your help!

BR, Mikko

[DataInsights]

@MikkoL,

I would add two tables to your model (if they don't already exist):

DimAccount

SlicerLevel

DimAccount would contain all accounts, and have a column Group Level that is set to 1 for all accounts that should be included when "Group" is selected in the slicer. DimAccount would have a one-to-many relationship to the fact table.

SlicerLevel would be a disconnected table with two rows (one for Company, and one for Group).

Here's an example of how the measure would look:

Amount =
IF (
    SELECTEDVALUE ( SlicerLevel[Slicer Value] ) = "Group",
    CALCULATE ( SUM ( FactTable[Amount] ), DimAccount[Group Level] = 1 ),
    SUM ( FactTable[Amount] )
)

[MikkoL]

Hello Datainsights,

Thank you for your proposal! I would elaborate my case a little bit more. In P&L I have built many functions to calculate e.g. gross margin, fixed costs etc. This has then been done three times in order to calculate actual figures, previous years figures and budget figures. To get the proposed solution to work with all the functions which use complicated row based logic would be challenging, but thank you for your proposal! BR, Mikko

[DataInsights]

@MikkoL,

Would you be able to provide a sample pbix (via OneDrive, etc.) illustrating the issue?