cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

## Request for help with DAX measure

Hi everyone,

I am having issues calculating a SUMX with a filter > 0 on a measure that is also being affected by period selection. Data model is a nice star schema with a Fact table where all the measures are coming from. I have a relative Period/Date slicer (coming from my Date table / Calendar Dimension) and three measures: a (running) Balance, which calculates the balance per day. Then we have an Allowed Balance (which is calculated dynamically based on the Period selection in my slicer), which has the following code:

Average allowed balance = CALCULATE([Allowed balance], ALLSELECTED(dim_date))

I use ALLSELECTED to get a desired average per period selection, so the number is not affected by the row context of the Date (days) in the visual. But [Allowed balance] is affected by the no. of days that are selected): Allowed balance = [IncomingItems]*[Norm]/[No. of days]

Then I have another measure which has this code:

Daily Balance Difference (DBD) = [Balance (at the end of the period)]-[Average allowed balance]

All well so far, it all works. But now I need to get a SUM of the DBD of all numbers higher then 0 (where i want to use SUMX in combination with FILTER, iterating over the Date table). When I apply the filter on the visual, the Average allowed balance recalculates, leaving me with the wrong values:

The main problem for me is that the measures need to work with the Periode/Date slicer, so i cannot use ALL(), but I am not sure how to proceed here. I just want the 'slice' the results without changing the values on Average allowed balance, but how?

(Sorry, no pbix example, it contains customer data).

Thanks in advance for the suggestions!

1 ACCEPTED SOLUTION
Community Support

Hi @Tamerlaaane ,

Measure =
SUMX (
FILTER (
SUMMARIZE (
'dim_date',
'dim_date'[Date],
"DBD", [Balance (at the end of the period)] - [Average allowed balance]
),
[DBD] > 0
),
[DBD]
)

Best Regards,
Gao

Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

3 REPLIES 3
Community Support

Hi @Tamerlaaane ,

Measure =
SUMX (
FILTER (
SUMMARIZE (
'dim_date',
'dim_date'[Date],
"DBD", [Balance (at the end of the period)] - [Average allowed balance]
),
[DBD] > 0
),
[DBD]
)

Best Regards,
Gao

Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

Frequent Visitor

Thanks so much, this works!

Frequent Visitor

Anyone? 🙂

Announcements

#### 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 Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors