Power BI Dynamic Column Selection based on Secondary Table filter

[Anonymous]

Hi Community Members,

I want to implement a solution where my aim is to achieve dynamic column selection based on the selection in Table 2. For example, the table 3 should display values for Region when region is selected from the filter above:

Table 3 = SELECTCOLUMNS('Table 1', "Dim", IF(SELECTEDVALUE('Table 2'[Dimension Select])="Region",'Table 1'[Region],"No value selected"))

However, It seems the false part in the if condition always works but not the true part. The selected dimension is "Region" and the table evaluates the false part but not the True part of the if condition.

Table 1:

Employee Competency Region Sector

Comp 1	Region 1	Sector1
Comp 2	Region 2	Sector 2
Comp 3	Region 3	Sector 3
Comp 4	Region 4	Sector 4
Comp 5	Region 5	Sector 5

Table 2:

Dimension Select          Dim_ID

Employee Competency	1
Region	2
Sector	3

I have a filter in my report from table 2 with for column "Dimension Select".

My table 3 should display the only 1 one column based on the selection from table 2 filter.

I am trying to achive this via "Table 3 = SELECTCOLUMNS('Table 1', "Dim", IF(SELECTEDVALUE('Table 2'[Dimension Select])="Region",'Table 1'[Region],"No value selected"))" DAX combination however somehow it's not working.

Kindly help..

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, i think you can not generate table dynamically by selecting slicer, but i have a workaround:

Please take following steps:

1)Add an index column to Table 1 in Query Editor.

2)Try this measure:

Measure = 
SWITCH (
    SELECTEDVALUE ( 'Table 2'[Dimension Select ] ),
    "Sector", MAX ( 'Table 1'[Sector] ),
    "Region", MAX ( 'Table 1'[Region] ),
    "Employee Competency", MAX ( 'Table 1'[Employee Competency] )
)

3)Choose [Index] from table 1, [Dimension Select ] from table 2 and this measure as a matrix visual:

4)Turn off 'Word Wrap' in Format/Column headers and Format/Row headers:

5)When you select one value in slicer, it shows:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[Anonymous]

Thank you so much. It's working as expected. Adding the index solved the problem. 🙂

[Anonymous]

Is this what you wanted? See the attached file.

Best

D

[Anonymous]

Hi darlove,

Thanks for sharing the file. Unfortunately I am not able to open the PBIX file. I would love to see your solution as well. I am getting below mentioned error while opening the file.

"Object reference not set to an instance of an object"

Feedback Type:
Frown (Error)

Error Message:
Object reference not set to an instance of an object.

Stack Trace:
at Microsoft.Mashup.Host.Document.PackageValidationResult.Merge(PackageValidationResult original, PackageValidationResult content)
at Microsoft.PowerBI.Client.Windows.Services.ReportFactory.CreateReportFromPowerBIPackage(IPowerBIPackage powerBIPackage, String pbixFilePath, Byte[] securityBindings, Boolean requiresConversionToReportLayout, PowerBIPackageOpenOptions options, IPowerBITelemetryService powerBITelemetryService, IAnalysisServicesService analysisServices, Version pbixFileVersion, PackageValidationResult& mashupValidationResult)
at Microsoft.PowerBI.Client.Windows.Services.PowerBIPackagingService.Open(FileStream fileStream, IPowerBIWindowService windowService, Nullable`1 entryPoint, PowerBIPackageOpenOptions options, ReportPreparerResult& prepareResult)
at Microsoft.PowerBI.Client.Windows.Services.FileManager.<LoadFromPbix>d__20.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.Client.Windows.Services.UIBlockingService.<>c__DisplayClass15_0`1.<<BlockUIAndRun>b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.PowerBI.Client.Windows.Services.UIBlockingService.WaitOnUIThreadForTaskCompletion(Task task)
at Microsoft.PowerBI.Client.Windows.Services.UIBlockingService.BlockUIAndRun[T](Func`1 asyncMethod)
at Microsoft.PowerBI.Client.Windows.Services.FileManager.OpenFile(IPowerBIWindowService windowService, IPbixFile fileToOpen, Nullable`1 entryPoint)
at Microsoft.PowerBI.Client.Program.TryOpenOrCreateReport(IUIHost uiHost, ISplashScreen splashScreen, IFileManager fileManager, IFileHistoryManager fileHistoryManager, String filePath, FileType fileType)
at Microsoft.PowerBI.Client.Program.<>c__DisplayClass2_0.<Main>b__1()
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<>c__DisplayClass3_0.<HandleExceptionsWithNestedTasks>b__0()
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)

Stack Trace Message:
Object reference not set to an instance of an object.

Invocation Stack Trace:
at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
at Microsoft.Mashup.Client.UI.Shared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace, String exceptionMessage)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.GetStackTraceInfo(Exception e)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass14_0.<HandleException>b__0()
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at Microsoft.PowerBI.Client.Program.Main(String[] args)

Thank you.

[Anonymous]

Hi there.

Here's a link to the file on my OneDrive. The advantage of the solution is that you don't have to define many columns in a table and then adjust, should you have more columns. It'll adjust itself to as many options as you have without a change to the code.

https://1drv.ms/u/s!ApyQEauTSLtOgYNLfOa1Owki8OfxYg?e=Xgj3U8

Best
D

[amitchandak]

Not very clear. We create a measure when we want ot use dynamic value from slicer