Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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.
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
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 ) )
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 ) )
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.
Can you give me an example using TREATAS() ?
@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
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
27 | |
20 | |
15 | |
12 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |