See just rows in common

[wribeiro]

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

[amitchandak]

@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

[wribeiro]

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.

[wribeiro]

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.

[v-yiruan-msft]

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

[wribeiro]

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.

[v-yiruan-msft · Accepted solution]

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