Building report through ACM connector

[eliskac]

Hi all,

I connected to the cost data via Azure Cost management connector in PBI desktop. My customer needs to build up a report where he'll see Charges against credit (adjustments), service overage and total charges per month. 

I looked into data model and tried to join two tables (Balance summary and Usage details) but I can't figure out through which column to join it because there's always M:N relationship.

Plus, I'm not sure how to calculate total charges (this column is in ACM PBI app but I can't see the detail of this measure).

Does anybody know how to calculate total charges and how to join the tables to get harges against credit (adjustments), service overage and total charges per month?

Thanks,

E.

[v-yangliu-msft]

Hi  @eliskac ,

You can create an Intermediate table to form a one-to-many method with the remaining two tables, and use the intermediate table to connect your model.

For example:

Table 1:

Table 2:

Form a many-to-many relationship;

Create an Intermediate table:

Then use Manager relationships to create the relationship between the table and the table, and the Intermediate table forms a one-to-many relationship with the other two tables:

After that, you can connect the intermediate table with your mode.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[eliskac]

Hi @v-yangliu-msft 

thank you for your advice. I did this. I actually need to connect "Balance summary" table to "Usage details". There are no common columns in these tables so I needed to join it via "Pricesheets" table since the relationship between "Balance summary" and "Pricesheets" was already established by default. So I wanted to join "Pricesheets" and "Usage details" and here I had the problem with M:N relationship. So I etablished this intermediate table with one column "Part number" and my model looks like this. 

However, when I built a visual and want to filter it by Date (from Usage details table) for which I created a slicer, it still doesn't work and doesn't filter by the date range I set up. It might be caused by the data under Contoso Demo EA account that I'm using. 

I just wanted you to have a look at this model view if you think it's correct. I also tried to change the direction of cross filter to work in both directions but it seemed it changed nothing in my case.

Thank you,

E.

[v-yangliu-msft]

Hi  @eliskac ,

You can upload the pbix file to the online drive, and then add the download link in the reply.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[eliskac]

Hi @v-yangliu-msft ,

I am sending the link here

https://microsofteur-my.sharepoint.com/:u:/g/personal/ecuhelova_microsoft_com/EWrG58mYUb5KgHyCM549HQ...

thank you,

E.

[v-yangliu-msft]

Hi  @eliskac ,

You can create relationships and see which column connects the two tables in the Manage relationships in the Model view

View relationship:

Enter the Manage relationships interface, you can see which columns are connected between the two tables

Create relationship:

Click New to enter the Create relationship interface, select two tables, and connect.

This is the link to the relevant documentation:

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand

To calculate the total cost, can you try to create a calculated column and use the Sum() function to get it directly.

Regarding harges against credit (adjustments), service overage and total charges per month, it is not very clear if there is no data. Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data, so that we can better help you solve.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[eliskac]

Hi @v-yangliu-msft, 

thank you for the reply. 

Yes, I know that I can manage relationships in the model view. I need to create a new relationship but there were always columns with M:N relationship. I tried to create a separate table with a column that has only unique values and connect those two tables though this one, but it seems that it still doesn't work. I guess I will have to keep trying.

I'm using Contoso Demo EA account and I've connected this account to PBI via ACM connector. I can't figure out where to insert pbix here in the reply. 

Thank you,

E.