Need Help on deciding DAX

[SidraB]

Hello Guys, I am working on PowerBI for the project of Sales automation, and I am directly connected to live data ( ERP is AS400), after data transformation and data modeling when I am trying to create a table for the sales value with regards to province, invoice no, invoice date, GL period and GL year for individual sales rep, but I am not getting a result, it's bringing lots of invoices that are not even in the month of September 2023, anyone can help me in this regard?

Best Regards,

Sidra Bushra

[SidraB]

This is my data model I want to calculate sales value based on GL year 2022 and 2023, and GL period 1 to 12, INLNYR, has invoice date, sales value information, INHDYR has GL period, GL Year and ARSLSM has Sales Rep information. I dont know which DAX should I use to extract this information.

[MarceloVieira]

@SidraB i dont know your knowledge of dax, but all those many to many relationships are very dungerous, very hard to follow the numbers/validations. I would start by "trying" to convert most if not all things, into many to 1 relationships.

[Erokor]

This helps a ton, thanks for posting that. Some of your problems are going to come from the fact that you have Many-to-Many relationships. I suggest you to think closely about what tables need to filter other tables then implement the proper filter directions.

For example: if you never filter the INLNYR table by the INHDYR table, then switch the filtering to have INHDYR only filter the INLNYR table. Thus the FxFactors table will chain through to the INLNYR table.

It appears that your INHDYR has multiple entries of GL period and GL Year (not sure what you are relating on, other than that you have a relationship). I suggest a stand-alone date table (the tool Bravo by the SQLBI.com folks can add one in pretty easily). Then relate two both fact dimensions. You'll want the unique relationships between the tables to be at the same grainularity (a good youtube search for Star Schema will help you understand this quickly).

[SidraB]

Hi Erokor, thanks for your response, based on the model I have created, can I merge the table like can I merge IN tables into one, AR tables into one, and ST tables into one in order to reduce the data load and get rid of the extra relationships I am getting, is this could be the solution?

[Erokor]

You can however, make sure that the Merge operation doesn't duplicate your data (you really have to test for this). I would worry about this working as you would expect, given the presence of the Many To Many relationships (and me not knowing what is making up the relationship keys).

[SidraB]

Thanks for your response, basically the company I am working for, uses an old 1980s AS400 system, and there is not much information available(for example no visual representation of the data model) and, I don't have much information about the primary key and foreign key, I have picked/choose tables based on the Access query(SQL View) which we use to pull information monthly for making manual sales report, and all the tables I have choose have one common key that is company no, but if can suggest, instead of making relationship, I should go with merging all the required tables? but I am not sure is it ok to do that.

[MarceloVieira]

@SidraBsorry if i will be obvious, but be very carefull with fact tables that "can" have multiple rows of versions of your SKs. Taking out of the away the versioned rows, you probably could join summarized (to certain level) data of other fact tables.

By version i mean, you have a unique field key, that repeats itself in other rows, because of some context of your columns changed, thus creating the row that reflect that change for just that specific column, ending with several rows that have all the history changes for a unique key field.

[ChiragGarg2512]

@SidraB Be sure to check the relationships and joins between the tables in Model View.

[Erokor]

Without seeing an example of your data model and DAX, I'd suggest checking your data types on both sides of your relationships and that your data model has proper relationships to begin with. If you'd like to post some more information someone here can likely help you!