Forum Discussion

gmatheus's avatar
gmatheus
Icon for Microsoft Employee rankMicrosoft Employee
3 years ago
Solved

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:   FactD...
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    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:

    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.

     

    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

v-yueyunzh-msft's avatar
v-yueyunzh-msft
Icon for Community Support rankCommunity Support
3 years ago

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

gmatheus's avatar
gmatheus
Icon for Microsoft Employee rankMicrosoft Employee
3 years ago

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=ZQoCs3

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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:

    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.

     

    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