User Profile
Dilbertfan
Frequent Visitor
Joined 2 years ago
User Widgets
Contributions
Re: Lookup & Concatenate Values in a Matrix
Hey ABD128 - This is fantastic, thank you so much - it worked how I wanted it to. I realised that I was trying to do this on real data (impossible to check all results) rather than do a sample Power BI Report which much simpler data, so I created something very small to also validate the results The only change I had to make to your code was in the followign where it wouldnt accept the Dataset ID for the new columns, but changing it to the original column name worked work. I left out the otucome for each intersection as realised it was pointless as they would be the same at each intersection of the matrix, so I can do a bit of conditional formatting on this. Thank you again for this. Whilst in my head I understood the steps to build the query, and I understand the logic of how you have written it, I couldn't have come up with this from scratch in terms of all the components. This forum constantly amazes me with how you can come up with these things based on an obscure requirement - even more impressive without any source data - and not strictly data analysis. Also how versatile a tool Power BI is. RichRe: Lookup & Concatenate Values in a Matrix
Hi freginier Firstly thanks for taking the time to respond and for a really comprehensive measure. Glad to see i wasn't totally on the wrong track. I am getting an error in the measure when it comes to the Crossjoin and using Selectolumns - see below. It obviously wants a table, but it thinks it is getting a string or numeric expression. I can't quite work this out as BaseDatasetsInColUseCase is a table and this continues in DatasetsForCurrentCol with either it being referred direct or with a filter which should result in a table again. If I remove the IF statement from DatasetsFromCurrentColumn then it works fine I have spent a bit of time playing with it, but still can't get it to accept the two variables - any ideas? However, If I do remove the IF statement but make sure that have the slicers set, the measure returns nothing in the matrix, so don't think the measure is working, which is strange as it all seems logical. Is it anything to do with that we can select multiple datasets and then compare all the use cases in each. Would the selected value take this into account Rich p.s. ignore me changing Dataset ID to Asset ID, it is just how we reference them now - in the above, I was trying to keep to the original question and the terminology used.Google Big Query Connector - May 25 Release
Hi All Wasn't sure whether to post this in Power Query or Desktop as I suppose it could fit in either. I have been trying to use the Revised Big Query Connector in the May 25 release of PBIDT. I have put in my Google Organisational Account Credentials and agreed that PBIDT can access my account. It then seems to connect: But when I try to drill down into the data, i get the following error: I thought that this was a driver error, but i have been told that the new version of the connector does not need a driver when connecting, and I haven't been able to find one for Power BI to Big Query in the ADBC format which i can get installed. I have a call in with IT about the Network or Proxy issues relating to the HTTP/2 protocol. Wonder if anyone can point me in the right direction Thanks in advance for your help RichLookup & Concatenate Values in a Matrix
Hi All Some time ago I posted a request for help (LINK) to compare two datasets names and their sensitivity, lookup up the combination value and then returning it at the intersection of a matrix view. barritown was really helpful in giving me the answer which worked fine. On the same report, I now have a related but different request which I have played around with for ages. Basically we have now extended the dataset name, so as well as a sensitivity value to compare, each dataset can have beetween 0 and five use cases with it. Each combination of use cases can be compared against each other for each datasets selected (the sensitivity doesn't matter - this is a different section of the report) I havent included a pbix or data file with this as not sure how the data should be structured for this. The Data In the original ask, each dataset had ONE sensitivity value We also now have the datasets with their use cases which need to be compared From this I have created a list by unpivoting which I think is needed to compare the two values. And then, I have created a disconnected version of this table so I have two identical tables which are can be filted by a slicer which is synced to a second disconnected slicer so that the datasets selected stay in sync (just like in the original above) Finally I have a look up table to compare the two values (This is part of it) The Ask What I would like to do is create a similar matrix to the sensitivity rating, but this time rather than the dataset being the row / column, I would like the use case to be the row column and then where they interset, to insert : the dataset combinations which match the intersection for each combination, look up the outcome and show this against the combination So you end up with something like this: I haven't put all the combinations in or put the right lookups in, but hopefully this gives you an idea. I have been looking at a CONCATENATEX measure along these lines which brings the tables together and then concatenates the datasets from dataset 1 and dataset 2 tables where they intersect the user cases. Look Up & Match Use Case (Concatenate Dataset) = VAR ds1 = SELECTEDVALUE('fctSiPAssets 1'[Dataset ID]) VAR ds2 = SELECTEDVALUE('fctSiPAssets 2'[Dataset ID]) VAR s1 = SELECTEDVALUE('fctSiPAssets 1'[UseCaseNo]) VAR s2 = SELECTEDVALUE('fctSiPAssets 2'[UseCaseNo]) VAR _temptable = FILTER( SUMMARIZECOLUMNS( 'fctSiPAssets 1'[Dataset ID], 'fctSiPAssets 1'[UseCaseNo], 'fctSiPAssets 2'[Dataset ID], 'fctSiPAssets 2'[UseCaseNo] ), 'fctSiPAssets 1'[Dataset ID] = ds1 && 'fctSiPAssets 2'[Dataset ID] = ds2 ) RETURN CONCATENATEX( _temptable, 'fctSiPAssets 1'[Dataset ID] & " | " & 'fctSiPAssets 2'[Dataset ID] & " - " & -- Need to put the outcome in here UNICHAR(10) ) However, this doesn't look right and certainly doesnt return the right result (although it does return a number of results, which leads me to think I am on the righ(ish) track and also doesn't solve how i look up the outcome for each dataset where they interest in the matrix. Hope that this makes sense; I thought I had my head around it after Barritone's solution for my previous and related requirement, but I just cant get it to work Thanks for this in advance and if you can let me know how to structure some data, happy to put tother a pbix file. RichHighlight Table Rows from Selection in Another Table
Hi All I have an issue with highlight rows in a table which I havent been able to find a suitable answer to from the various posts on highlighting rows using disconnected tables. The Data I have two tables - one for Employee and one for Topic, with a 1-Many relationship between them The Ask I would like to be able to select an Organisation which should show all of the disctinct topics for that organisation in a table and the employees in another table. However, I would then like the user to be able to click on the employee name and for it to highlight the topics that they are interested in - something like the following layout. So if Bob was clicked, Mental, Cycling and Yoga would be highlighted. I dont want to use a filter on the Topic table (which would be the easy way out) as I want to see continually see all of the topics for the organisation. I have tried various combinations of the suggestions by Goodly and Valerie Junk from YouTube, but to no available. If necessary, I am happy for the name to be in a slicer visual, as I can use the tile slicer to add in their job description which I havent shown on the data table. Hope this is enough information. A sample pbix file is attached Highlight Test File Thanks in anticipation for your help Rich
