counting by joining tables

[kasife]

Hi guys,
I'm having trouble coming up with a solution. I have the following measure that counts and joins my tables that are related through the broker ID. It turns out that in these tables there is information from 2 systems, so it can happen that 1 broker has 2 IDs, so the count is doubled. Is there the possibility of adjusting this measurement so that it performs this union using the broker ID, but gives me a different count using the broker name?
Thank you in advance.

COUNTROWS(DISTINCT(UNION(
    SELECTCOLUMNS(_leads,"Broker",[idcorretor]),
    SELECTCOLUMNS(_pastas,"B",[idcorretor]),
    SELECTCOLUMNS(_ccvs,"B",[idcorretor]),
    SELECTCOLUMNS(_repasses,"B",[idcorretor]))))

[MFelix · Accepted solution]

Hi @kasife ,

In this select you do not have any name so you cannot make a count based on the name.

When you refer that the tables are related based on broker do you have a separate that identifies each broker and is ID?

If yes you could try something similar to this:

Count Brokers =
VAR _selectedBroker =
    DISTINCT (
        UNION (
            SELECTCOLUMNS ( _leads, "Broker", [idcorretor] ),
            SELECTCOLUMNS ( _pastas, "B", [idcorretor] ),
            SELECTCOLUMNS ( _ccvs, "B", [idcorretor] ),
            SELECTCOLUMNS ( _repasses, "B", [idcorretor] )
        )
    )
VAR _brokerstable =
    FILTER ( BrokersTable, BrokersTable[idcorretor] IN _selectedBroker )
RETURN
    COUNTROWS ( DISTINCT ( SELECTCOLUMNS ( _brokerstable, [name] ) ) )

[kasife]

@MFelix Thanks for the answer.
I don't have another table, and my dimension table is related to the fact tables by broker id

[MFelix]

Hi @kasife 

The other table I refer is the broker table that you use has a dimension.

[kasife]

Thank you so much @MFelix . it worket.