Forum Discussion
Create button to copy selected rows from visual table into a new table?
Super UserIn simple terms - every visual you see rendered on a Power BI report page or a dashboard tile is backed by a DAX query that is run against the underlying semantic model, with the filter context applied. You can see that query when you "export to Excel with live connection" in a regular report, or when you use Performance Analyzer.
In an embedded scenario you can choose to let the Power BI renderer display the visuals inside the embedded component, or you can run the DAX query and render the results yourself, outside of the embedded component.
As the dax query is dependant on the selected rows, e.g:
// DAX Query DEFINE VAR __DS0FilterTable = FILTER( KEEPFILTERS(VALUES('NOTIFICATION_SYSTEM_STATUS'[SYSTEM_STATUS_TEXT_EN])), OR( SEARCH("NOPR", 'NOTIFICATION_SYSTEM_STATUS'[SYSTEM_STATUS_TEXT_EN], 1, 0) >= 1, SEARCH("OSNO", 'NOTIFICATION_SYSTEM_STATUS'[SYSTEM_STATUS_TEXT_EN], 1, 0) >= 1 ) ) VAR __DS0FilterTable2 = TREATAS({"A", "B", "C", "D", "E"}, 'OpsAreas'[Shift]) EVALUATE SUMMARIZECOLUMNS( __DS0FilterTable, __DS0FilterTable2, "SelectedFilters", IGNORE('SAPDataSnowflake'[SelectedFilters]) )
If NO rows are selected, and:
// DAX Query DEFINE VAR __DS0FilterTable = FILTER( KEEPFILTERS(VALUES('NOTIFICATION_SYSTEM_STATUS'[SYSTEM_STATUS_TEXT_EN])), OR( SEARCH("NOPR", 'NOTIFICATION_SYSTEM_STATUS'[SYSTEM_STATUS_TEXT_EN], 1, 0) >= 1, SEARCH("OSNO", 'NOTIFICATION_SYSTEM_STATUS'[SYSTEM_STATUS_TEXT_EN], 1, 0) >= 1 ) ) VAR __DS0FilterTable2 = TREATAS({"A", "B", "C", "D", "E"}, 'OpsAreas'[Shift]) VAR __DS0FilterTable3 = TREATAS( {("000021295192", "inlet isoaltion valve passes when closed", DATE(2018, 11, 14), 64.171428571428578, "D", DATE(2018, 12, 19), BLANK()), ("000021291381", "Crossover pipe compensator mis-alignment", DATE(2018, 8, 28), 66.4, "B", DATE(2018, 10, 2), BLANK()), ("000021297098", "HTA Biocide system missing labels", DATE(2019, 1, 2), 105.28571428571429, "E", DATE(2019, 1, 23), "02.01.2019 16:46:42 CET Sarah Gerry (S13829) HTA Bicoide system missing labels. 1. KKS non existent on the skid itself. Please label. 2. KKS within the SAP Maintenance system: There is dulpication within SAP with regards to KKS, consequence of this confuses the user which KKS to use when placing notifications. Please cleanse."), ("000021277237", "U7 incorrect alarm", DATE(2017, 10, 9), 120.90909090909091, "A", DATE(2017, 10, 31), BLANK()), ("000021251528", "A Site statiion drains", DATE(2016, 6, 4), 150.14285714285714, "D", DATE(2016, 6, 25), BLANK())}, 'SAPDataSnowflake'[NOTIFICATION_ID], 'SAPDataSnowflake'[NOTIFICATION_DESCRIPTION], 'SAPDataSnowflake'[NOTIFICATION_DATE], 'PriorityCalculations'[Risk Factor], 'OpsAreas'[Shift], 'SAPDataSnowflake'[REQUIRED_ENDDATE], 'NOTIFICATION_LONG_TEXT'[LONG_TEXT] ) EVALUATE SUMMARIZECOLUMNS( __DS0FilterTable, __DS0FilterTable2, __DS0FilterTable3, "SelectedFilters", IGNORE('SAPDataSnowflake'[SelectedFilters]) )
if rows are selected.
The issue I'm having is refereing to a DAX formula when the result is dynamic. Given that Dax query changes every time is there a way of checking which visuals have been selected. You can use the measure:
SelectedFilters = IF( ISFILTERED(SAPDataSnowflake[NOTIFICATION_ID]), CONCATENATEX( VALUES(SAPDataSnowflake[NOTIFICATION_ID]), SAPDataSnowflake[NOTIFICATION_ID], ", " ), "No Filters Applied" ) to create a list of chosen id's but the DAX will var for whatever rows selected.
Sorry formatting issues:
That method won't work As the dax query is dependant on the selected rows, e.g:
// DAX Query DEFINE VAR __DS0FilterTable = FILTER( KEEPFILTERS(VALUES('NOTIFICATION_SYSTEM_STATUS'[SYSTEM_STATUS_TEXT_EN])), OR( SEARCH("NOPR", 'NOTIFICATION_SYSTEM_STATUS'[SYSTEM_STATUS_TEXT_EN], 1, 0) >= 1, SEARCH("OSNO", 'NOTIFICATION_SYSTEM_STATUS'[SYSTEM_STATUS_TEXT_EN], 1, 0) >= 1 ) ) VAR __DS0FilterTable2 = TREATAS({"A", "B", "C", "D", "E"}, 'OpsAreas'[Shift]) EVALUATE SUMMARIZECOLUMNS( __DS0FilterTable, __DS0FilterTable2, "SelectedFilters", IGNORE('SAPDataSnowflake'[SelectedFilters]) )
If NO rows are selected, and:
// DAX Query DEFINE VAR __DS0FilterTable = FILTER( KEEPFILTERS(VALUES('NOTIFICATION_SYSTEM_STATUS'[SYSTEM_STATUS_TEXT_EN])), OR( SEARCH("NOPR", 'NOTIFICATION_SYSTEM_STATUS'[SYSTEM_STATUS_TEXT_EN], 1, 0) >= 1, SEARCH("OSNO", 'NOTIFICATION_SYSTEM_STATUS'[SYSTEM_STATUS_TEXT_EN], 1, 0) >= 1 ) ) VAR __DS0FilterTable2 = TREATAS({"A", "B", "C", "D", "E"}, 'OpsAreas'[Shift]) VAR __DS0FilterTable3 = TREATAS( {("000021295192", "inlet isoaltion valve passes when closed", DATE(2018, 11, 14), 64.171428571428578, "D", DATE(2018, 12, 19), BLANK()), ("000021291381", "Crossover pipe compensator mis-alignment", DATE(2018, 8, 28), 66.4, "B", DATE(2018, 10, 2), BLANK()), ("000021297098", "HTA Biocide system missing labels", DATE(2019, 1, 2), 105.28571428571429, "E", DATE(2019, 1, 23), "02.01.2019 16:46:42 CET Sarah Gerry (S13829) HTA Bicoide system missing labels. 1. KKS non existent on the skid itself. Please label. 2. KKS within the SAP Maintenance system: There is dulpication within SAP with regards to KKS, consequence of this confuses the user which KKS to use when placing notifications. Please cleanse."), ("000021277237", "U7 incorrect alarm", DATE(2017, 10, 9), 120.90909090909091, "A", DATE(2017, 10, 31), BLANK()), ("000021251528", "A Site statiion drains", DATE(2016, 6, 4), 150.14285714285714, "D", DATE(2016, 6, 25), BLANK())}, 'SAPDataSnowflake'[NOTIFICATION_ID], 'SAPDataSnowflake'[NOTIFICATION_DESCRIPTION], 'SAPDataSnowflake'[NOTIFICATION_DATE], 'PriorityCalculations'[Risk Factor], 'OpsAreas'[Shift], 'SAPDataSnowflake'[REQUIRED_ENDDATE], 'NOTIFICATION_LONG_TEXT'[LONG_TEXT] ) EVALUATE SUMMARIZECOLUMNS( __DS0FilterTable, __DS0FilterTable2, __DS0FilterTable3, "SelectedFilters", IGNORE('SAPDataSnowflake'[SelectedFilters]) )
if rows are selected.
The issue I'm having is refereing to a DAX formula when the result is dynamic. Given that Dax query changes every time is there a way of checking which visuals have been selected. You can use the measure:
SelectedFilters = IF( ISFILTERED(SAPDataSnowflake[NOTIFICATION_ID]), CONCATENATEX( VALUES(SAPDataSnowflake[NOTIFICATION_ID]), SAPDataSnowflake[NOTIFICATION_ID], ", " ), "No Filters Applied" )
to create a list of chosen id's but the DAX will var for whatever rows selected.