Anonymous

Not applicable

4 years ago

Solved

Comparing two table columns which is having Many to many relationship

i have a two table in power bi . Both tables have a columntype as a common value. its a Many to many relationship.

my dataset looks like below

Out_DataComparison_Totals Table

ColumnType IN_Tables EO_IN_Tables EO_Out_Tables CalendarYear

Adjust 500 400 478 2021

Adjust 1000 1000 1000 2022

Demand 500 1400 478 2021

Demand 2500 2500 2500 2022

vw_IntegrationDataCheck table

ColumnType CalendarYear CMN

Adjust 2021 300

Adjust 2022 1000

Demand 2021 750

Demand 2022 2500

Next after relationship i have CMN value into Out_DataComparison_Totals tables.
Now Out_DataComparison_Totals tables looks like .i want to create extra column for true or fale .

if all values matched column should return True .
if all not values matched column should return fales.

ColumnType IN_Tables EO_IN_Tables EO_Out_Tables CalendarYear CMN Status

Adjust 500 400 478 2021 478 False

Adjust 1000 1000 1000 2022 1000 True

Demand 500 1400 478 2021 750 False

Demand 2500 2500 2500 2022 2500 True

i have created below measure

Check = IF(Out_DataComparison_Totals[IN_Tables]=Out_DataComparison_Totals[EO_IN_Tables]&&Out_DataComparison_Totals[EO_IN_Tables]=Out_DataComparison_Totals[EO_Out_Tables]&&Out_DataComparison_Totals[EO_Out_Tables]=RELATED(vw_IntegrationDataCheck[CMN]),"True","False")

But getting following warning Error

The column 'vw_IntegrationDataCheck[CMN]' either doesn't exist or doesn't have a relationship to any table available in the current context.

any idea . how to resolve this warning Error . thanks in advance.

Anonymous you can write 2 measures like this

CMN =
CALCULATE (
    MAX ( vw_IntegrationDataCheck[CMN] ),
    TREATAS (
        SUMMARIZE (
            Out_DataComparison_Totals,
            Out_DataComparison_Totals[ColumnType],
            Out_DataComparison_Totals[CalendarYear]
        ),
        vw_IntegrationDataCheck[ColumnType],
        vw_IntegrationDataCheck[CalendarYear]
    )
)


STATUS =
VAR _others =
    AVERAGEX (
        UNION (
            { MAX ( Out_DataComparison_Totals[EO_IN_Tables] ) },
            { MAX ( Out_DataComparison_Totals[IN_Tables] ) },
            { MAX ( Out_DataComparison_Totals[EO_Out_Tables] ) }
        ),
        [Value]
    )
RETURN
    IF ( [CMN] = _others, TRUE (), FALSE () )

or a combined measure like this

Combined =
VAR _cmn =
    CALCULATE (
        MAX ( vw_IntegrationDataCheck[CMN] ),
        TREATAS (
            SUMMARIZE (
                Out_DataComparison_Totals,
                Out_DataComparison_Totals[ColumnType],
                Out_DataComparison_Totals[CalendarYear]
            ),
            vw_IntegrationDataCheck[ColumnType],
            vw_IntegrationDataCheck[CalendarYear]
        )
    )
VAR _others =
    AVERAGEX (
        UNION (
            { MAX ( Out_DataComparison_Totals[EO_IN_Tables] ) },
            { MAX ( Out_DataComparison_Totals[IN_Tables] ) },
            { MAX ( Out_DataComparison_Totals[EO_Out_Tables] ) }
        ),
        [Value]
    )
RETURN
    IF ( _cmn = _others, TRUE (), FALSE () )

The pbix is atatched

_manyToManyTREATAS.pbix33 KB

6 Replies

VahidDM
Super User
4 years ago
Anonymous

It seems this post is same as your previous post, I shared a solution in the previous one, please check and let me know if that does not help.

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Comparing-two-table-columns-which-is-having-Many-to-many/m-p/2244609#M53826

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

smpa01

Community Champion

4 years ago

Anonymous you can write 2 measures like this

CMN =
CALCULATE (
    MAX ( vw_IntegrationDataCheck[CMN] ),
    TREATAS (
        SUMMARIZE (
            Out_DataComparison_Totals,
            Out_DataComparison_Totals[ColumnType],
            Out_DataComparison_Totals[CalendarYear]
        ),
        vw_IntegrationDataCheck[ColumnType],
        vw_IntegrationDataCheck[CalendarYear]
    )
)


STATUS =
VAR _others =
    AVERAGEX (
        UNION (
            { MAX ( Out_DataComparison_Totals[EO_IN_Tables] ) },
            { MAX ( Out_DataComparison_Totals[IN_Tables] ) },
            { MAX ( Out_DataComparison_Totals[EO_Out_Tables] ) }
        ),
        [Value]
    )
RETURN
    IF ( [CMN] = _others, TRUE (), FALSE () )

or a combined measure like this

Combined =
VAR _cmn =
    CALCULATE (
        MAX ( vw_IntegrationDataCheck[CMN] ),
        TREATAS (
            SUMMARIZE (
                Out_DataComparison_Totals,
                Out_DataComparison_Totals[ColumnType],
                Out_DataComparison_Totals[CalendarYear]
            ),
            vw_IntegrationDataCheck[ColumnType],
            vw_IntegrationDataCheck[CalendarYear]
        )
    )
VAR _others =
    AVERAGEX (
        UNION (
            { MAX ( Out_DataComparison_Totals[EO_IN_Tables] ) },
            { MAX ( Out_DataComparison_Totals[IN_Tables] ) },
            { MAX ( Out_DataComparison_Totals[EO_Out_Tables] ) }
        ),
        [Value]
    )
RETURN
    IF ( _cmn = _others, TRUE (), FALSE () )

The pbix is atatched

_manyToManyTREATAS.pbix33 KB

smpa01
Community Champion
4 years ago
Anonymous did you have a chance to look into it?
- Anonymous
  Not applicable
  4 years ago
  smpa01 thank you for you reply .let me check will update you .

Forum Discussion

Comparing two table columns which is having Many to many relationship

6 Replies

Related content

Relationship

Relationships

Relationship Issue

Creating relationships

PowerBI Relationships