Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Keep initial balance but remove blanks rows

Hi! 

I have the following scenario:

I'm trying to remove all blank rows based on year selection, but keeps the initial balance for those non blank rows.

 

I'm using the following measure to calculate the values in the matrix:

 

Initial Balance Year =
VAR CurrentYear = SELECTEDVALUE('Balance Date'[Year])
VAR Balance= sum('Fact_table'[amount])
VAR DateStart = CALCULATE(MIN('Date Budget'[Date]), ALLSELECTED())
VAR OpenBalance = CALCULATE('Fact_table'[amount], 'Date Budget'[Date] < DateStart)
VAR Total = not ISINSCOPE('Balance Date'[Year])
VAR Result =
    if(CurrentYear = "Initial Balance", OpenBalance,
        if(Total, (sum('Fact_table'[amount])+ OpenBalance, (sum('Fact_table'[amount])))
RETURN
    Result

 

Is there any way to do it?

 

Thanks

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,

     

    Based on your description, I use the sample data in PowerBI Desktop and can filter the matrix by creating a measure.

     

    Here to determine if the profit is greater than 100000:

     

    Measure = IF(MAX('financials'[Profit])>100000,1,0)

     

     

    Add a filter to this visual:

     

    Result:

     

     

    Best Regards,
    Zhu

     

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

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ,

     

    Based on your description, I use the sample data in PowerBI Desktop and can filter the matrix by creating a measure.

     

    Here to determine if the profit is greater than 100000:

     

    Measure = IF(MAX('financials'[Profit])>100000,1,0)

     

     

    Add a filter to this visual:

     

    Result:

     

     

    Best Regards,
    Zhu

     

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