I've been trying to get an answer for this problem over so many places but I haven't found a solution yet.
I have one fact table and three dimensions on my model real simple, like this:
FactDetails TenantId, GroupId, Qty..
I would like to show if all GroupId from DimensionGroupsConfigured has any value (True or False from column IsGroupInFactTable) on FactDetails when filtered by column, which is TenantId in this case. I have all relationships actives and they are all "Both" directions unless DimensionGroupsConfigured, because I want to see every row from that table.
So if I filter the TenantId equals "11aa11aa-1111-111a-a1a1-1111aa1guilh" from the DimensionCustomers, I would like to show for that specific tenant, if has any GroupId configured from DimensionGroupsConfigured, which in that case doesn't have any, so all rows should be FALSE.
What can I do? Is there a Dax or Calculated Column for that? How could I create a better model for that situation?
The image below is showing True, because it's scanning in all table instead of just that particulally tenant.
1) Yes, the relationship between all dimensions and fact areone-to-many. 2) The fields are from dimensions
3) You can find sample data attached in the PBIX file.
I already let filtered, and created two simple tables to show what is happening. In the table below is showing two "True", but it should be showing only one, because that tenant has only one GroupId that are under DimensionGroupsConfigured. While in the table above, the GroupId is coming from DimensionGroups to show all groups that tenant has, despite if is in DimensionGroupsConfigured or not.
But if NO tenant is filter from DimensionCustomers, then show if any of GroupId from DimensionGroupsConfigured constains in fact table.
Thank you for providing us you sample data.According to your description, you want to filter the [TenantId] in 'DimensionCustomers' table, and you want to display True or False in 'DimensionGroupsConfigured' table where the [GroupId] is related to the [TenantId] you have filtered. Right?
Here are the steps you can follow:
(1)You can create a measure : "Test"
Test = var _tenant_table= VALUES('DimensionCustomers'[TenantId])
var _fact_tabele=DISTINCT( SELECTCOLUMNS( FILTER(ALLSELECTED('FactPoliciesGroupsDetails'), 'FactPoliciesGroupsDetails'[TenantId] in _tenant_table) , "GroupId" , [GroupId]))
IF( SELECTEDVALUE('DimensionGroupsConfigured'[GroupId]) in _fact_tabele , TRUE(),FALSE())
(2)We can put the [Test] measure in your below table, then you can filter the [TenantID] in 'DimensionCustomers' table and the True or False will change when you filter.
But in the data you provide, the TenantId is '96e9f546-8e5c-48b4-a45d-d45abc6bb4b4'. I filter it in 'FactPoliciesGroupsDetails' table , it has this GroupId:
There are two same GroupId in 'DimensionGroupsConfigured' table .
(3)So the result is as follows:
If this method cannot help you ,can you provide your output sample data as a table form so that we can help you better.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly