Forum Discussion

gonrodrigues's avatar
gonrodrigues
Icon for Helper III rankHelper III
10 years ago

Measure or Power Query?

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!!

Need Help

6 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Icon for Community Support rankCommunity Support
    10 years ago

    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

     

     

     

     

     

     

     

      • v-qiuyu-msft's avatar
        v-qiuyu-msft
        Icon for Community Support rankCommunity Support
        10 years ago

        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