Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
i need to create a calculatedtable with data that is from 3 tables, like the picture below.
i have 2 tables connected through the dates table, and other (tax) connected to payments table.
what i need is :
OBJ_ID (from invoices), taxA, taxB and the values that they have on the Tax table ? How can i do this ?
thanks,
JP
Solved! Go to Solution.
I have used a similar structure to the one you have given. Can you see if the calculate table sample I provided in my earlier message helps?
thank you guys, Meanwhile, i've upload the imagem with the relatioships.
daxeralmighty, you're right, but my sources are in excel files, and thats how they arrive to me..
I've the OBJ_ID that are in the invoices table.
than i've to match in payments table, those who are in the invoices.
and i want to bring, for each row (that exists in invoices and payments table) the 2 Tax (A and B) with the corresponding values.
thanks for the help,
JP
Seeing you have a 1:* relationship between the payments and tax table, you can keep that relationship. Just create a Dimension table for Obj_ID and link to Payments and Invoices in a 1:* relationship between the dimension table and payments and invoices table. Then just create the visual using the fields from the Dimension Obj_ID and/or the date table. You can then add fields from the invoices, payments and tax tables in you visuals
you can create the Obj-ID dimension table using the following DAX in a new table:
Dim Obj_ID =
VAR Inv = VALUES ( Invoices[Obj-ID])
Var Paym = VALUES (Payments[Obj_Id])
RETURN
DISTINCT(UNION(Inv, Paym))
Proud to be a Super User!
Paul on Linkedin.
I have used a similar structure to the one you have given. Can you see if the calculate table sample I provided in my earlier message helps?
I agree with the users that the schema seems to be incorrect. But for what it's worth the following will get you the result (Note: it may not be the most efficient method since more details about the tables are required)
CalTable =
ADDCOLUMNS (
Invoices,
"TAX_A", LOOKUPVALUE ( Tax[TaxA], Tax[ObjID], Invoices[ObjID] ),
"TAX_B", LOOKUPVALUE ( Tax[TaxB], Tax[ObjID], Invoices[ObjID] )
)
Create a dimension table with unique values for Obj_ID, delete the relationship between payments and tax tables, and create a one-to-many relationship between the Dim OBj and the three fact tables.
Proud to be a Super User!
Paul on Linkedin.
The schema is at least incomplete. We don't know what kind of relationships there are between the tables, what the Date dimension points to in the related tables and why there's no relationship between the Invoice dimension and the Payments fact table. Because of the latter, the schema seems to be incorrect.
Can you please tell me why the same "Date" table needs to be connected to the Invoice and Payments table?
Also is the relationship between Payments and Tax 1:1 or 1:*?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
87 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |