Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
My goal is to combine the results of two data tables.
I have a Data model that contains four tables at all:
My goal is to count the number of relvant car customers and sum up the number of corresponding subscription licenses.
1st step is to define relevant car customers. Relevant means that they have to buy at least 100 units of car products. This targets only Table2.
2nd step is to count the number of relevant car customers. This targets only Table2.
3rd step is to sum up the number of subscription licenses of relevant car customers. This targets Table1 and Table2.
I have already found a solution for the the first two steps (maybe a bad one, but it is working).
But for the 3rd step I haven`t found a solution so far.
Data:
Table1:
Date | Customer | Subscription Licenses |
2022-01-31 | 001 | 10 |
2022-01-31 | 002 | 12 |
2022-01-31 | 003 | 15 |
2022-01-31 | 004 | 20 |
2022-02-28 | 001 | 11 |
2022-02-28 | 002 | 12 |
2022-02-28 | 003 | 17 |
2022-02-28 | 004 | 20 |
… | … | … |
Table2:
Date | Customer | Product Category | Product | Amount |
2022-01-31 | 001 | House | Door | 100 |
2022-01-31 | 001 | House | Roof | 50 |
2022-01-31 | 001 | Car | Tyre | 250 |
2022-01-31 | 002 | Car | Tyre | 40 |
2022-01-31 | 002 | Car | Wheel | 10 |
2022-02-28 | 001 | House | Door | 80 |
2022-02-28 | 001 | Car | Tyre | 150 |
2022-02-28 | 002 | Car | Wheel | 120 |
… | … | … | … | … |
uniqueTable:
Customer |
001 |
002 |
003 |
004 |
... |
calender:
Date |
2022-01-31 |
2022-02-28 |
… |
For step 1+2 my solution that I found so far looks as follows:
step1:
relevant_Car_customer =
VAR _limit = 100
VAR _salesAmount
CALCULATE(
SUM('Table2'[Amount]),
FILTER('Table2'[Product Category] = "Car")
)
RETURN
IF(
_salesAmount >= _limit,
1,
0
)
step2:
number_of_relevant_Car_customers =
SUMX(
VALUES('Table2'[Customer]),
[CAR_customer]
)
The relationships are defined as follows:
My desired solution looks as follows:
Date | number of customers | number of subscription licenses |
2022-01-31 | 1 | 10 |
2022-02-28 | 2 | 23 |
Explanation:
Thank you very much for your help!
Best
Andre
Solved! Go to Solution.
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
69 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |