Forum Discussion

pi8080's avatar
pi8080
Regular Visitor
2 years ago
Solved

Hide rows if a column is empty

Hi all,   I am trying to build a DAX which would show only those periods where we have both actuals and budget data.   Let's assume I have 12 periods, one for each month of the year and that I ha...
  • pi8080's avatar
    pi8080
    2 years ago

    Hi Yulia, thanks for your reply.

     

    I found the solution by adding a filter using Actuals Posting Period. Now only the periods displayed in the Actuals table will be shown in the PowerBI table which is what I wanted. ( Before i could see 12 periods even though I had only two months worth of data for Actuals). The only downside with this method is that the slicer is visible in the report view.

     

    Thanks again

    Piero

     

    Before I could see 12 periods in the BI table cause the Budget file has 12 periods but by using the filter I can match it to the actuals periods so tba

pi8080's avatar
pi8080
Regular Visitor
2 years ago

I found another formula which show just the periods when both columns have data however it doesnt calculate the variance between the two columns and the actuals column display the same amount in both rows in period 1 and 2 ( the amount displayed in each row is the total amount)

 

The formula is = If(ISFILTERED(Actuals[postingperiod]),calculate(sum(actuals[actual]),FILTER('actuals',NOT('actuals'[postingperiod] in VALUES('actuals'[posting period])))))

pi8080's avatar
pi8080
Regular Visitor
2 years ago

Sorry I forgot to mention that I added a slicer with the posting period from the Actuals table that drives what periods should be displayed