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
Meghbajaj
Frequent Visitor

Need Help with a Measure

Overview:
I have a date based table in which I have the following columns:
ID, Date (from today's date to historical date) (ex. March 21 2023, March 20 2023, March 19 2023 etc),  Plan Type and Amount.

I am using the following formula:

Measure 6 =
CALCULATE(SUM('Worker Compensation Detail All'[PAY RATE_]),
FILTER('Worker Compensation Detail All','Worker Compensation Detail All'[Date Key] = max('Worker Compensation Detail All'[Date Key])),ALL('Worker Compensation Detail All'[PLAN TYPE]))

The sum I am getting at the bottom is correct. However, I need the column of 'Measure 6'. I guess it has something to do with the Filter context of Plan Type. 

Meghbajaj_1-1679440167659.png

I need help fixing my measure to get the required result. Thank you in advance.


 

3 REPLIES 3
igrandey89
Advocate II
Advocate II

Try replacing the [Date Key] with whatever ID column you're using in the date table, or the field used in the relationship between the two tables.

igrandey89
Advocate II
Advocate II

Potential issue with the 'Measure 6' formula is related to the filter context of the 'Plan Type' column. When you use the ALL function on the 'Plan Type' column, it removes the filter context of that column, which is causing the same value to appear in every row of the 'Measure 6' column.

To fix this issue, you can use the VALUES function to get a table of distinct values in the 'Plan Type' column and iterate over them using the SUMX function. Here is an updated formula that should work:

Measure 6 =
SUMX (
    VALUES ( 'Worker Compensation Detail All'[PLAN TYPE] ),
    CALCULATE (
        SUM ( 'Worker Compensation Detail All'[PAY RATE_] ),
        FILTER (
            ALL ( 'Worker Compensation Detail All'[PLAN TYPE] ),
            'Worker Compensation Detail All'[Date Key] = MAX ( 'Worker Compensation Detail All'[Date Key] )
        )
    )
)

I hope I am understanding your issue correctly and hope this helps

 

Hi @igrandey89 , thanks for replying back. I am still having some trouble:

Meghbajaj_0-1679469992664.png


 

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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