DirectQuery Visual with multiple datasets not working

[BethC901]

I have 2 datasets in a Premium workspace.

In PowerBI desktop using DirectQuery, I connected to both datasets.  

I created a relationship between  Dataset1 - Table A and Dataset2 - Table A as a one:many single filter.  The join line on the diagram has a broken link symbol on it.  I don't know the name of that type of symbol.

I created a table visual with a column from Dataset1 - Table A which works fine.  When I add a column from Dataset2 - Table A, it does not work.  

How do I make the relationship work between these two tables from different datasets?

[v-shex-msft]

Hi @BethC901,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[BethC901]

Certainly not frustrating me.  I appreciate the help.  The two tables in question are Constituent and STG ConstituentAppeal.  Here is the properties showing Storage Mode of DirectQuery and the relationship between the two.

[Anonymous]

Ok, that helps a ton.  I mis-interpreted your initial post.  I don't believe you can directly relate two published datasets.  Something to try (that I haven't yet) is in PoweryQuery, Reference "Constituents" as a new table and then join that new table instead.  Worth a shot

[AlexisOlson]

This relationship connection line with the gaps confirms that this is a "limited relationship" as described here:

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand

[BethC901]

Thank you for that explanation.  If cross-island tables (which I think means from 2 datasets) are always limited relationships, then it seems there's not much point in using them.  My intent in designing a tabular model was to create a common dataset that contains table data used in all types of reports, and then datasets for the various departmental needs.  However, if I cannot join the common dataset with the departmental dataset in Power BI, this method will not work.   Do I need to have all the tables in 1 huge dataset or is there another way?

[Anonymous]

I'm totally with you and believe this to be a gap in the PowerBI service as a whole.  As BI developers, we want to keep our source of truth as consolidated as much possible to avoid "multiple" sources of truth.  We have the same problem in our org, where we would like to publish "standard" datasets like dimensions etc. that all other teams could leverage in there work, so that PersonA, PersonB etc. is represented consistently across the enterprise.  Your approach totally makes sense, however, the capability is only available in Premium capacity.  

For now though, how many tables does your common dataset have? Is it feasible to abstract them as calculated tables (as needed) so you can still leverage the common dataset?  I realize it's extra work and not ideal but it's one way to get around the limitation for now.

[BethC901]

To further explain:

I have a slicer the selects a subset of records in STG ConstituentAppeal which is similar table to Constituents.  I attemped a calculated table with this DAX:

NewTable =

VAR Table2 = SUMMARIZECOLUMNS('STG ConstituentAppeal'[MKTSegmentationID],

FILTER('STG ConstituentAppeal', 'STG ConstituentAppeal'[MKTSegmentationID]= SELECTEDVALUE('STG MKTSegmentation'[PrimaryKey])))

RETURN

FILTER('STG ConstituentAppeal',

'STG ConstituentAppeal'[MKTSegmentationID] IN Table2)

The SelectedValue is the selected key in the slicer.  It only has 168,000 rows.  

However, the filter is not working and it still returns 1M+ rows with an error.

Then I tried this DAX:

NewTable =

var SelectedIssue = SELECTEDVALUE('STG MKTSegmentation'[PrimaryKey])

return

SELECTCOLUMNS(

FILTER (

'STG ConstituentAppeal',

'STG ConstituentAppeal'[MKTSegmentationID] = _Measures[SelectedIssue]

),

"AKey",'STG ConstituentAppeal'[PrimaryKey],

"Segment",'STG ConstituentAppeal'[MKTSegmentationID]

)

The measure looks like this.  I tested it by putting it on a card, and it works.

SelectedIssue = SELECTEDVALUE('STG MKTSegmentation'[PrimaryKey])

Same problem though.  Still returns 1M+ records.

[Anonymous]

I'm still digesting this a bit but looking at your final measure below

NewTable =
var SelectedIssue = SELECTEDVALUE('STG MKTSegmentation'[PrimaryKey])
return
SELECTCOLUMNS(
FILTER (
'STG ConstituentAppeal',
'STG ConstituentAppeal'[MKTSegmentationID] = _Measures[SelectedIssue]
),
"AKey",'STG ConstituentAppeal'[PrimaryKey],
"Segment",'STG ConstituentAppeal'[MKTSegmentationID]
)

Did you mean to use VAR SelectedIssue as your filter value where you have _Measures[SelectedIssue]  ??

[BethC901]

Apologies.  I've tried both.  But I now understand that a slicer cannot be used to filter a calc table.  Therefore, I have no idea how to create that much needed table.

[AlexisOlson]

FYI, calculated tables cannot be responsive to slicers since they are only computed when the model is loaded or refreshed, not in response to slicers, filters, or other user interactions.

[BethC901]

Thanks.  I found that out the hard way.  Problem is that I don't know how to filter my table down so it will fit into a calculated table.  

[BethC901]

I am working in Premium capacity if that makes a difference.

I attempted a calculated table from Constituents.  However, that table is 7M+ records and it will not process.  I would be fine with creating all calculated tables (I have about 5 common tables), but I've run into this 1M record limit brick wall.  Help??  

[Anonymous]

@BethC901 , @AlexisOlson is right.  PowerBI can't traverse that relationship directly.  I jumped into one of my Premium workspaces and played around with it but couldn't get the relationships to work directly but I was able to get around it.

This is the basic idea:

What you can do is create a calculated table

=SELECTCOLUMNS('Constituents',[PrimaryKey],[Name],[KeyName])

Then create the relationship to that table instead.  Worked good for me.  The data produced from relating my calculated table (based on Dataset2) to a fact table in (Dataset1) produced the results i expected.

[AlexisOlson]

It might be a limited relationship. See this article for details:
https://www.sqlbi.com/articles/strong-and-weak-relationships-in-power-bi/

[Anonymous]

Are the tables using different storage modes, one using "import" and the other using "DirectQuery?"

[BethC901]

They are both directquery that have been deployed to Premium workspace via Visual Studio.  

[Anonymous]

Interesting.  I normally only see this when there is a discrepancy between the related tables such as an import table related to a direct query tables. In that scenario, switching the Imort table to Dual usually resolves it.  Are you positive that both tables are set to DQ?  You can create a model as DirectQuery, but still inadverntaly pull tables in as Import.  Not trying to frusterate you but it's happened to me.

in case you don't know where to look at each table individually: