Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Filtering around the "Active" Value in a chart

Hello,

 

I have a Matrix of Orders that shows year and month of the orders as columns, the region of the orders on the rows, and a measure as a value.

 

I need the measure to be all orders that were committed before the month being displayed in the matrix and completed after the month. 

 

For example, let's pretend, we are looking at the month of May, 2018 in the Matrix.  That month should look at all the commit dates that came in months before it, and the realized dates that came in months after it.  If we look at the table below, the result in the Matrix in Power BI should show 8.

 

Production Year-Month Commit Production Year-Month Realized

201804201806
201804201807
201804201806
201804201806
201804201806
201804201806
201804201808
201804201809

 

I have a formula that does half the filter but I can't for the life of me figure out how to put the 2nd part of the filter and get it working in a Matrix as expected.

 

 

Production Test = CALCULATE(COUNT('Order Book'[Production On Time]), 
                                FILTER('Order Book', SELECTEDVALUE('Order Book'[Production Year-Month Commit]) > 'Order Book'[Production Year-Month Commit])
                                )

 

 

I'm using the "SelectedValue" function so I can reference which month the Matrix is calculating currently against the entire Order Book table.  I'm assuming this is incorrect. 

 

But, if this is correct, I can't place a second filter in here where I write something like:

 

 

Production Test 3 = CALCULATE(COUNT('Order Book'[Production On Time]), 
                                FILTER('Order Book', SELECTEDVALUE('Order Book'[Production Year-Month Commit]) > 'Order Book'[Production Year-Month Commit] &&
                                                     SELECTEDVALUE('Order Book'[Production Year-Month Realized]) < 'Order Book'[Production Year-Month Realized])
                                )
                              

 

I'll just get no results.  My matrix is the following with the first formula (Production Test) that I pasted above:

 

Picture 1.png

Essentially, I need a measure that will show 8 for the month of May where it performs both filters I need and not just the 1st filter.  Any suggestions to what I'm doing wrong here?

 

Thanks!

5 REPLIES 5
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @Anonymous ,

 

If I understand your sceanrio correctly that you have two slicers for Production Year-Month Commit and Production Year-Month Realized? 

 

By my tests, I cannot reproduce your scenario, if it is convenient, could you share your data sample and your desired output so that I could have a test on it.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-piga-msft ,

 

I can't seem to attach a file to this message but I'm placing a Dropbox link which hopefully you should be able to access.  It is a sample file that uses a sample dataset and explains my problem which I tried to explain here.  If you can take a look and let me know if there is any additional information I would need to provide, that would be great!

 

https://www.dropbox.com/s/rj6btudom7tqijd/Sample%20Data.pbix?dl=0

 

Thank you.

 

Anonymous
Not applicable

Anyone have any help on this?  I still haven't been able to figure it out.  My last message with my sample file explains the issue much better.

 

Thanks.

Anonymous
Not applicable

Still trying to see if anyone has a suggestion for this. Been stuck on this problem for weeks now and it seems like it would be a simple solution.
Anonymous
Not applicable

Hi @v-piga-msft ,

 

Did you get a chance to view the sample file I provided?  Wondering if there is any additional information that I should provide to help out with a resolution on this problem.

 

Thank you.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.