Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Power BI Dynamic Column Selection based on Secondary Table filter

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 Regi...
Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago
      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