Measure or Power Query?

[gonrodrigues]

Hello!

I have created 2 calculated columns:

This column counts the number of rows

QP Row Count = IF(COUNTROWS(RELATEDTABLE(QP))>0;COUNTROWS(RELATEDTABLE(QP)); 0)

And this one makes the difference from prior month

Difference From Prior Month = 'Variação'[QP Row Count] - CALCULATE(SUM('Variação'[QP Row Count]);DATEADD('Variação'[Date];-1;MONTH))

Here is the Table

To create a chart like this

What's the problem? With calculated columns PBi doesn't allow me to create a relationship with Date table due to 'Circular dependency' error and without that i cannot use the proper filter from Date table.

Should i create a measure or power query? and how?

Thanks!!

[v-qiuyu-msft]

Hi @gonrodrigues,

You mentioned "With calculated columns PBi doesn't allow me to create a relationship with Date table due to 'Circular dependency' error and without that i cannot use the proper filter from Date table.", do you mean you want to filter waterfall chart based on the value from Date table, right?

I have followed your steps to create two calculated columns and a waterfall chart, everything is fine. In your scenario, you need to check relationship among QP, Variação and Date tables. Please refer to screenshots below:

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[gonrodrigues]

@v-qiuyu-msft

Can you help me doing these 2 columns in power query?Probably work this way 🙂

[gonrodrigues]

Hello @v-qiuyu-msft

absolutely right!

[v-qiuyu-msft]

Hi @gonrodrigues,

In your original post, you mentioned you create two calculated columns: [QP Row Count] and [Difference From Prior Month] in table Variação, then you need to create a membership between Variação and QP tables instead of Variação and Data tables.

If issue persists, please share .pbix file for our analysis if possible.

Best Regards,
Qiuyun Yu

[gonrodrigues]

hey @v-qiuyu-msft

Said I needed to create a membership between variação and Data tables 🙂

Unfortunately, I cannot share .pbix file, it has confidential info

[v-qiuyu-msft]

Hi @gonrodrigues,

Sorry for the late response. Without .PBIX file, I'm not able to analyze the issue closely. Would you please display the image about the relationship between all tables in your report? Perhaps I can find anything from there.

Besides, please take a look at this similar thread: Circular dependency detected.

Best Regards,
Qiuyun Yu