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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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