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

[MS22]

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.

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.

[v-shex-msft · Accepted solution]

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