cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## See just rows in common

Hi guys,

It is a simple question, but I need your help. I have two facts tables. they have 3 common dimensions.

Fact1

Fact2

Dimension1

Dimension2

Dimension3

I have a report using dimension 2 and sum(Fact1.value) and sum(Fact2.value)

I want to see in this report, only Dimension1.code that I have in common in both fact tables.

It is important to emphasize that I am not using in this report the Dimension1.code.

It is like a Filter page, but i need to consider just common codes (dimension1.code).

Could you help me? Is it a filter? specific measure? What do you suggest in that case?

@Greg_Deckler , any idea?

Thank you

1 ACCEPTED SOLUTION
Community Support

Hi @wribeiro ,

You can create a measure as below and apply the visual-level filter to filter the data with the condition(Flag is 1😞

``````Flag =
VAR _selcode =
SELECTEDVALUE ( 'Client'[cod_clie] )
VAR _tab1 =
CALCULATETABLE (
VALUES ( 'Fact1'[Cod_cli] ),
FILTER ( 'Fact1', 'Fact1'[Cod_cli] = _selcode )
)
VAR _tab2 =
CALCULATETABLE (
VALUES ( 'Fact2'[Cod_cli] ),
FILTER ( 'Fact2', 'Fact2'[Cod_cli] = _selcode )
)
RETURN
IF ( _selcode IN _tab1 && _selcode IN _tab2, 1, 0 )``````

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Super User

@wribeiro , Assume you have two measures F1 and F2 from two facts then code that are common

Countx(filter(Values(Dimension1[Code]) , not(isblank([F1])) && not(isblank([F1]))  ), [Code])

you can use sumx and measure in place of code to sum only common values

Helper I

Thank you, @amitchandak your solution works properly. My question is: is it possible to do it just by applying filters? I would like avoiding to create another measure for that.

Helper I

In that case, I need to implement in my measures, am I right? Could I do it in another way? I am asking about the other way because, sometimes I would like to analyze just commons and in another moment, all of things.

Community Support

Hi @wribeiro ,

You can refer the following links to get the common rows in different tables.

Get only matching rows in different tables when filtering on date

INTERSECT – finding of common rows in two tables (DAX – Power Pivot, Power BI)

And as you said, you may need to get the data with different requirement(filters), so could you please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

Hi, thank you for your mensage and support.
you are able to see below the model and the PBX file.

I would like to see in my bar chart, just common clients and both fact tables. Sometimes I want to see all clients, sometimes, just common clients in both fact tables. Unfortunately, clients are dynamic and I can't manually apply a filter.https://drive.google.com/file/d/1FV6TAAAKHoPMt4xE14Ge8bahAXaqm7I5/view?usp=sharing

It will be like a select distinct in both cod client (Fact 1 and Fact2) applying and intersection and apply the result, filtering in the entire Dashboard.

Community Support

Hi @wribeiro ,

You can create a measure as below and apply the visual-level filter to filter the data with the condition(Flag is 1😞

``````Flag =
VAR _selcode =
SELECTEDVALUE ( 'Client'[cod_clie] )
VAR _tab1 =
CALCULATETABLE (
VALUES ( 'Fact1'[Cod_cli] ),
FILTER ( 'Fact1', 'Fact1'[Cod_cli] = _selcode )
)
VAR _tab2 =
CALCULATETABLE (
VALUES ( 'Fact2'[Cod_cli] ),
FILTER ( 'Fact2', 'Fact2'[Cod_cli] = _selcode )
)
RETURN
IF ( _selcode IN _tab1 && _selcode IN _tab2, 1, 0 )``````

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors