Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gmatheus
Employee
Employee

Check if value is another table when filtered

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..

 

DimensionGroups

GroupId, GroupName

 

DimensionCustomers

TenantId, TenantName

 

DimensionGroupsConfigured

GroupId, IsGroupInFactTable

 

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.

gmatheus_0-1663795474726.png

 

 

 

1 ACCEPTED SOLUTION

Hi , @gmatheus 

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]))
return 
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:

vyueyunzhmsft_0-1663902061263.png

There are two same GroupId in 'DimensionGroupsConfigured' table .

 

(3)So the result is as follows:

vyueyunzhmsft_1-1663902134644.png

 

If this method cannot help you ,can you provide your output sample data as a table form so that we can help you better.

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

3 REPLIES 3
v-yueyunzh-msft
Community Support
Community Support

Hi, @gmatheus 

I have a few questions about your description:

(1)Whether the dimension table and the fact table are both one-to-many relationships?

(2)Are the fields you want to place in the visual from a fact table or a dimension table?

(3)Can you provide some sample data in tabular form (or .pbix file) and provide us with the output data you want in tabular form?

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Hi @v-yueyunzh-msft!

 

1) Yes, the relationship between all dimensions and fact are one-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.

 

PBIX file (one drive): https://microsoft-my.sharepoint.com/:u:/p/gmatheus/EYP1RihnPcZEhiDhlrLT4VQBoWbu-7GPub0Ni1N-bve-dA?e=...

Hi , @gmatheus 

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]))
return 
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:

vyueyunzhmsft_0-1663902061263.png

There are two same GroupId in 'DimensionGroupsConfigured' table .

 

(3)So the result is as follows:

vyueyunzhmsft_1-1663902134644.png

 

If this method cannot help you ,can you provide your output sample data as a table form so that we can help you better.

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors