Forum Discussion
Helper Vcounting by joining tables
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]))))
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] ) ) )
4 Replies
- MFelix
Super User
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] ) ) )
