Forum Discussion

unknown_anony's avatar
unknown_anony
Icon for Helper II rankHelper II
3 years ago
Solved

Filter Dimension table by facts table

Hello I have a emp master table. I have a fact table with only managers related calculations. There are few of the managers not listed in the fact table. How would i find the list of the managers which are present in the dimension table and not in the fact table.

Emp id is the primary key in the dimension table. Connecting both the tables in 1 to many relationship  .
relevant screenshot is also attached below.

The formula what i have writen in the dimenson table filtering the facts  is. 

New Column = CALCULATE(FIRSTNONBLANK(SOT[GEID],1),FILTER(SOT,SOT[GEID] = EMP_MS[Manager Geid])).
SOT[GEID] is the managers id in facts table which contains duplicate vales. and EMP_MS[GEID] is the primary key column in dimension table
I am not getting the proper outcome with this new column.
Any suggestion are welcom
Thank You
Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , unknown_anony 

    According to your description, do you mean you want to get the  list of the managers which are present in the dimension table and not in the fact table based on the 'Dim_table'[All Emp ID] and 'Fact Table'[manager id].

    And in my understand , the result is like this:

    If this , you can create a calcualted column using this dax code:

    Column = var _t =  DISTINCT('Fact Table'[manager id])
return
IF([All Emp ID] in _t , 0 ,1)

     

    If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    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

     

1 Reply

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

    Hi , unknown_anony 

    According to your description, do you mean you want to get the  list of the managers which are present in the dimension table and not in the fact table based on the 'Dim_table'[All Emp ID] and 'Fact Table'[manager id].

    And in my understand , the result is like this:

    If this , you can create a calcualted column using this dax code:

    Column = var _t =  DISTINCT('Fact Table'[manager id])
return
IF([All Emp ID] in _t , 0 ,1)

     

    If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    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