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.
Hi All.
I'm attempting to create an attribute table from two fact tables in DAX. I'm fully aware that this is far easier and better to do in PowerQuery except that I'm using a database that has slow performance factor and the query takes forever to evaluate (even though I'm only pulling in a small amount of data). As an alternative I'd like to built this attribute table within DAX.
I want to pull just the distinct attributes from both fact tables so I can explode that within a table visual. These two facts are:
FACT_1
Ticket_ID | Country | Amount | SoldTo | SalesType | YearMonth | Date | ID.Key |
1 | US | 100 | Party1 | Sales Order | 202001 | 1-Jan-20 | 1^202001 |
2 | US | 102 | Party1 | Sales Order | 202001 | 1-Jan-20 | 2^202001 |
3 | CAD | 104 | Party2 | Sales Order | 202001 | 1-Jan-20 | 3^202001 |
4 | CAD | 500 | Party3 | Sales Order | 202001 | 1-Jan-20 | 4^202001 |
5 | US | 5410 | Party1 | Sales Order | 202001 | 1-Jan-20 | 5^202001 |
6 | CAD | 20 | Party4 | Sales Order | 202001 | 1-Jan-20 | 6^202001 |
7 | CAD | 80 | Party3 | Sales Order | 202001 | 1-Jan-20 | 7^202001 |
2 | US | 20 | Party1 | Sales Order | 202001 | 1-Jan-20 | 2^202001 |
5 | US | 5400 | Party1 | Credit | 202001 | 1-Jan-20 | 5^202001 |
5 | US | 400 | Party1 | Sales Order | 202001 | 1-Jan-20 | 5^202001 |
FACT_2
Ticket_ID | Amount | Approved | Customer | YearMonth | Date | ID.Key |
1 | 100 | 1 | Customer 1 Name | 202001 | 1-Jan-20 | 1^202001 |
2 | 102 | 0 | Customer 1 Name | 202001 | 1-Jan-20 | 2^202001 |
3 | 104 | 1 | Customer 2 Name | 202001 | 1-Jan-20 | 3^202001 |
4 | 500 | 0 | Customer 3 Name | 202001 | 1-Jan-20 | 4^202001 |
5 | 5400 | 1 | Customer 1 Name | 202001 | 1-Jan-20 | 5^202001 |
5 | 10 | 1 | Customer 1 Name | 202001 | 1-Jan-20 | 5^202001 |
Pretty common scenario. If this is done in PowerQuery, I'd LEFTOUTERJOIN the FACT_1 ID.KEY to FACT_2 ID.KEY and return the distinct table values. This image result below is what I want:
As such, when I add this with my bridge table I'm retrieving the values I want:
So my attempt at DAX does not yield the expected result:
Any ideas on how to proceed?
Solved! Go to Solution.
@v-juanli-msft ,
Thanks for the reply. Unfortunately I was looking for a route that did not consider using any relationships (active or inactive) while actively avoiding M-2-M. Considering performance vs data model size I opted with my original solution to use PowerQuery
Hi @hnguyen76
two ways to get expected result as you provided:
1. create relationships
2. create inactive relationships(inactive relationships won't filter tables)
Create calcualted columns
amount2 = CALCULATE(SUM(Table2[Amount]),USERELATIONSHIP(Table1[ID.Key],Table2[ID.Key]))
customer = LOOKUPVALUE(Table2[Customer],Table2[ID.Key],Table1[ID.Key])
@v-juanli-msft ,
Thanks for the reply. Unfortunately I was looking for a route that did not consider using any relationships (active or inactive) while actively avoiding M-2-M. Considering performance vs data model size I opted with my original solution to use PowerQuery
You can use a cross join in Dax but it will make things slow. You can use it inside the filter to have your condition.
Also, use selectcolumns to rename and select columns as this will not Allow same name column.
Use summarize of top of it.
CROSSJOIN is definitely out of the question. I tried it with my dataset yesterday and it returned me 2 billion rows and took about 20 minutes to evaluate.
I think the option you planning to use of power query is better. What you actally want to achieve using merge
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 |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |