Conditionally Filtering Out Data

DAX DAX Calculations
1

I am still new to Power BI and I run into stuff that seems easy, but apparently is not for me yet.

I have created two measures via the “SUM” function. The Agreement Invoiced from Table A and Agreement Charged from Table B. Both tables are tied together by a Customer Look Up Table which contains Customer Name. I put the Customer Name in a matrix along with Agreement Invoiced and Agreement Charged and all works perfectly. I changed the matrix to a bar chart. Now, I only want to display the customer if Agreement Charged is >= Agreement Invoiced. The data is on the chart…I have it tied together by customer…but I am starting to think I cant filter because they are two sums…am I missing something or is this harder than I am thinking? Thanks for the help.

2

First always try to work these out when the results are in a table rather than an image, so you can actually see the numbers at work.

Also I can’t tell if you are using DAX to create your filtered results here. I’m suspecting no, but can’t tell for sure.

If you want to created some filtered results in a visual alongside other you most likely wanting to be using some combination of CALCULATE and FILTER.

See below for more information.

http://portal.enterprisedna.co/courses/108877/lectures/2000577

http://portal.enterprisedna.co/courses/108877/lectures/2000623

http://portal.enterprisedna.co/courses/108877/lectures/2000638

You need to be diving into DAX functions as soon as possible. Everything should be done in DAX.

Past this I would need to see more to help I think. If you can upload a demo file of the scenario that always helps as well.

This doesn’t sound difficult at all. There’s just a few fundamental things that you need to master in Power BI to see how easy these can be to work out.

These include concepts like ‘context’ being the main one I believe.

Here’s some videos to go over.

http://portal.enterprisedna.co/courses/108877/lectures/2000594

http://portal.enterprisedna.co/courses/108877/lectures/2000597

Really dive into the Mastering DAX Calculation course module when you can.

Thanks
Sam

3

I try to do everything in Dax. I have uploaded the PBIX file. The problem is in the second tab. As you can see, the two measures represent fine. Now I want to create a visual where it only displays if one is larger than the other…coming from two tables, I am not sure how to get there. I have the two measures…but not sure I am approaching it the right way. 2019 Agreement Analysis.pbix (1.0 MB)

4

Ok that’s great regarding DAX.

Now looking at the data this isn’t too difficult at all really I don’t believe.

First thing I did though was changed the results into a table so I could actually see the numbers. Highly recommend always doing this.

image
image1208×412 24.9 KB

I think that all this requires is a simple IF statement

Something like this

Charged > Invoiced = IF( [Charged Agreement Only] > [Total For Agreements Invoiced], [Charged Agreement Only], BLANK() )

image
image942×410 26.6 KB

Then if you turn it into a visual you’ll just get the customers you need

image
image683×552 24.2 KB

If you set your model up well, like you have, then all of this logic can be completed quite easily using measure branching techniques.

Sam

1 Like