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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ChuckChuck
Helper I
Helper I

Use a variable in a measure as a filter within same measure

I have a measure that I need to take one step further, but can't figure out the solution to make it work. Using the below measure I can determine the variance for each customer. However, I only want to return the results if the variance shows underperformance (i.e. variable Underperforming >0). Is it possible to add this last filter to the measure so that only underperforming values are picked up? Each time I try a filter, it's looking for a table, but I want the filter to be on the variable itself.  Currently it's retruning True or False, but I want the actual dollars and only where it's underperfoming. 

 

Total Month Expected vs Actual =

    VAR

    Underperforming = 

        CALCULATE(

        [Month Expected]-[Month Actuals],

        FILTER(Table1,[Frequency]="Only Once") ,

        FILTER(Table2,[Date]=Table3[Max Expected Date]))

    Return

Underperforming>0

13 REPLIES 13
Zubair_Muhammad
Community Champion
Community Champion

@ChuckChuck

 

Do you need this???

 

Total Month Expected vs Actual =
VAR Underperforming =
    CALCULATE (
        [Month Expected] - [Month Actuals],
        FILTER ( Table1, [Frequency] = "Only Once" ),
        FILTER ( Table2, [Date] = Table3[Max Expected Date] )
    )
RETURN
    IF ( Underperforming > 0, underperforming )

 


Regards
Zubair

Please try my custom visuals

Thanks, @Zubair_Muhammad! Almost, but not exactly. I got this far too, but gave up on this version of the measure because won't actually display the grand total of just the underperforming values. If in a table I can get the expected results to appear, but the grand total for the table is still showing the total without that last filter applied (so ignoring if it's underperforming or not). I also have a card visual and since the net of all accounts is not underperforming, the value displayed in the card is not correct. 

HI @ChuckChuck

 

Normally you can use this pattern to get correct totals for the MEASURE

Try creating another MEASURE on these lines

 

New Measure =
IF (
    HASONEFILTER ( TableName[ColumnName] ),
    [Total Month Expected vs Actual ],
    SUMX (
        ALLSELECTED ( TableName[ColumnName] ),
        [Total Month Expected vs Actual ]
    )
)

Regards
Zubair

Please try my custom visuals

Hi @Zubair_Muhammad. I've added the second measure to reference the first. However, the results are simply adding up the total of all values and not just the total where the customer is underperforming to expectations. 

Hello,
If I correct undrestud, you are tracking performance by Customer. Try this approach (in example I use dimension table Customer with attribute Customer Name):

 

Total Month Expected vs Actual =
SUMX (
    ADDCOLUMNS (
        VALUES ( Customer[Customer Name] ),
        "UnderPerforming", CALCULATE (
            [Month Expected] - [Month Actuals],
            FILTER ( Table1, [Frequency] = "Only Once" ),
            FILTER ( Table2, [Date] = Table3[Max Expected Date] )
        )
    ),
    IF ( [UnderPerforming] > 0, [UnderPerforming] )
)

Thanks, @popov. I've given this a try, but the resutls where only returned for one of the 15 customers and those results weren't correct. If I remove the SUMX part and just do the ADDCOLUMNS part, I get an error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." Do you know what this indicates?

Hello, @ChuckChuck
Sorry for my delay. Can you share your formula (based my formula) and screenshots for current and expected result?

Hi @popov, I made some test data and applied the measure to it and then created some screen grabs. This is close to what I need, but it is picking up the values for dates I'm not interested in and it's not giving me a total, just blank. 2018-03-19_9-06-32.jpg

 

 

 

Hi, @ChuckChuck

Try this formula

Total Month Expected vs Actual =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( Sheet1, Sheet1[Customer Name], Sheet1[Expected Date] ),
        "UnderPerforming", CALCULATE (
            [Month Expected] - [Month Actuals],
            FILTER ( Sheet1, [Frequency] = "Only Once" )
        )
    ),
    IF ( [UnderPerforming] > 0, [UnderPerforming] )
)

Hi @popov, Unfortunately this version still isn't producing the desired results. In fact, when I add a table to just show the measure for each customer name the table is excluding records and the measure doesn't add up correctly. Here's a screenshot:

2018-03-21_7-07-06.jpg

Hello, can you share your pbix file to test?

Hi @popov

I don't see an option to attach a PBIX file. Were you able to get the formula to work on a similar set of data on your end?

Hello, @ChuckChuck

You can upload it to OneDrive or Dropbox and post the link here.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.