cancel
Showing results for 
Search instead for 
Did you mean: 
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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.