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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
MS22
New Member

Use filter from 3 different related tables and change cardinality of 2 tables to both

I have 3 fact tables and multiple dimention tables of their own. Only one dimention table is common to all 3 fact tables (employee department). There is a common condition to be used in all the measures i.e these exlusions are to be used for all the calulations/measures. I am planning to create a calculated column (flag) or handling these exclusions in each measure.
star.png
Need help to write DAX of these exclusions. Condition should be like this:

calculate(
   xyz,
   D_fact1 = "abc",
   Isblank( D_fact2 ) || D_Fact3 <> "...",
   CommD_ Fact1 = "HR",
   CommD_fact2 = "Sales"

)

 

if I will create a flag column in Fact1 then,

IF(
   D_fact1 = "abc" &&
   Isblank( D_fact2 ) ||

   D_Fact3 <> "..." &&
   CommD_ Fact1 = "HR" &&
   CommD_fact2 = "Sales",
   "Y",
   "N"
)

This is just a sample code for explaining my situation. There could be use of CROSSFILTER() to set direction to both, RELATED(), FILTER(), ALL(), multiple CALCULATE() and what not. Please help me design this solution or suggest if another better approach should be taken.

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

HI @MS22,

AFAIK, current power bi desktop does not support create dynamic calculate column/table based on filter. They do not work on the same levels, and you can't use child level to affect their parent.

For exclusion specific filter effects, I'd like to suggest you take a look at the following blog about All functions if helps:

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT - SQLBI

In addition, please also check the relationship directions to confirm these filters are correctly apply to your tables:

Bi-directional relationship guidance - Power BI | Microsoft Learn

Notice: the data level of power bi(from parent to child level)

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

HI @MS22,

AFAIK, current power bi desktop does not support create dynamic calculate column/table based on filter. They do not work on the same levels, and you can't use child level to affect their parent.

For exclusion specific filter effects, I'd like to suggest you take a look at the following blog about All functions if helps:

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT - SQLBI

In addition, please also check the relationship directions to confirm these filters are correctly apply to your tables:

Bi-directional relationship guidance - Power BI | Microsoft Learn

Notice: the data level of power bi(from parent to child level)

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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