Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 dime...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Anonymous ,

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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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.

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    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