Two tables no relationship measure

[aslure]

I have two tables that do not have a relationship defined between them. I want to create a measure that will give me the percentage of unique IDs by location. I also have slicers on the dashboard that will slice table_2 so that I can also look at each location percentage by different slicers (ex. gender, ethnicity).

Table_1 (Treat this table as your numerator in the percent calculation)

Employee_ID	Gender	Ethnicity	Location
1234	Male	Asian	Detroit
1235	Female	African American	Denver

Table_2 (Treat this table as your denominator in the percent calculation)

Employee_ID	Gender	Ethnicity	Location
1234	Male	Asian	Detroit
1235	Female	African American	Denver
1236	Female	White	Denver
1237	Female	Middle Eastern	Denver
1238	Male	Asian	Detroit
1239	Male	African American	Detroit

DAX Measure I wrote does work when you are looking at the total number from each table, however when you place it on a chart it quickly messes up and the the slicers also seem to screw up the calculation.

table_1_num = DISTINCTCOUNT(table_1[Employee_ID])

table_2_num = DISTINCTCOUNT(table_2[Employee_ID])

location_percent = DIVIDE(table_1_num,table_2_num,0)

When I place location_percentage on a chart and add the location from table_2 it is erroring out.

I am missing something here because I need to make a relationship somewhere in the DAX or in Model to make sure if I change the slicer selection my percentages will change but not error out.

[v-kkf-msft]

Hi @aslure ,

Please modify [table_1_num] to:

table_1_num = 
CALCULATE (
    DISTINCTCOUNT ( 'Table 1'[Employee_ID] ),
    FILTER (
        'Table 1',
        'Table 1'[Location] = MAX ( 'Table 2'[Location] )
            && 'Table 1'[Gender]
                IN ALLSELECTED ( 'Table 2'[Gender] )
                    && 'Table 1'[Ethnicity] IN ALLSELECTED ( 'Table 2'[Ethnicity] )
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1 · Accepted solution]

H i @aslure 
Here is a sample file for your reference https://www.dropbox.com/t/FxhaDftl5wTE1VHx
You can create a common unique table using this code

Table = DISTINCT ( UNION ( table_1, table_2 ) )

Then create the relationships

Then create your measure

Percentage = 
VAR table_1_num = 
    DISTINCTCOUNT ( table_1[Employee_ID] )
VAR table_2_num = 
    DISTINCTCOUNT ( table_2[Employee_ID] )

VAR Result = 
    DIVIDE ( table_1_num, table_2_num, 0 )
RETURN
    Result 

[tamerj1]

Hi @aslure 

did you have a chance to have a look at my reply?

[aslure]

Just tested this solutoin, but both tables do not have the same # of columns so this command fails.

Table = DISTINCT ( UNION ( table_1, table_2 ) )

[tamerj1]

@aslure 

We can fix that

Table =
VAR T1 =
    SELECTCOLUMNS (
        table_1,
        "@ID", table_1[Employee_ID],
        "@Ethnicity", table_1[Ethnicity],
        "@Gender", tabel_1[Gender],
        "@Location", tabel_1[Location]
    )
VAR T2 =
    SELECTCOLUMNS (
        table_2,
        "@ID", table_2[Employee_ID],
        "@Ethnicity", table_2[Ethnicity],
        "@Gender", tabel_2[Gender],
        "@Location", tabel_2[Location]
    )
RETURN
    DISTINCT ( UNION ( T1, T2 ) )

[Anonymous]

You can use TREATAS() in order to create relationship only for the scope of the measure, while having the tables unrelated in the data model.

[aslure]

Can you give me an example using TREATAS() ?

[amitchandak]

@aslure , You need to have common dimension between two tables to analyze data together Ethnicity, Location, Gender and employee ID etc

location= distinct(union(distinct(Table1[location]),distinct(Table2[location])))

Join with both tables

Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19

[aslure]

Is there a way to create a table with multiple columns with distinct values for each of their respective columns. For example lets say I want to a table that only has distinctive values from each of the two tables where the column names match?