Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes! Register now.

Reply
Pandadev
Post Prodigy
Post Prodigy

I have a measure which is working with a single selection from a filter , need it for multi choice

‎11-07-2023 12:08 AM

Hi I have maanged to create this dax measure , which works fine if just a single filter selection is made , but when I add more than one filter it shows the total value , as if no filters have been  applied , is there a way to amend it so that it works , no matter how many of the filters are chosen .

Cumulative - Cost =
VAR CurrentMonth = MAX('Data_Cost_List'[Date])
VAR SelectedCostType = SELECTEDVALUE('Data_Cost_List'[Cost Type])
VAR SelectedCostGroup = SELECTEDVALUE('Data_Cost_Profiles'[Cost Group])
VAR SelectedDataset = SELECTEDVALUE('Data_Cost_Profiles'[Dataset])
VAR SelectedDataOwner = SELECTEDVALUE('Data_Cost_Profiles'[Data Owner])
VAR SelectedCurrentApprover = SELECTEDVALUE('Data_Cost_Profiles'[Current Approver])
VAR SelectedCurrentVendor = SELECTEDVALUE('Data_Cost_Profiles'[Vendor Name])
VAR SelectedCurrentVendorType = SELECTEDVALUE('Data_Cost_Profiles'[Vendor Type])
RETURN
IF (
    NOT ISFILTERED('Data_Cost_Profiles'[Cost Group]) && NOT ISFILTERED('Data_Cost_Profiles'[Dataset])&& NOT ISFILTERED('Data_Cost_Profiles'[Data Owner])&& NOT ISFILTERED('Data_Cost_Profiles'[Current Approver])&& NOT ISFILTERED('Data_Cost_Profiles'[Vendor Name])&& NOT ISFILTERED('Data_Cost_Profiles'[Vendor Type])&& NOT ISFILTERED('Data_Cost_List'[Cost Type]),
    CALCULATE(
        SUM('Data_Cost_List'[Value]),
        FILTER(
            ALL('Data_Cost_List'),
            'Data_Cost_List'[Date] <= CurrentMonth
        )
    ),
    CALCULATE(
        SUM('Data_Cost_List'[Value]),
        FILTER(
            ALL('Data_Cost_List'),
            'Data_Cost_List'[Date] <= CurrentMonth
                && (
                    ISBLANK(SelectedCostType) || 'Data_Cost_List'[Cost Type] = SelectedCostType
                )
                && (
                    ISBLANK(SelectedCostGroup) || RELATED('Data_Cost_Profiles'[Cost Group]) = SelectedCostGroup
                )
                && (
                    ISBLANK(SelectedDataset) || RELATED('Data_Cost_Profiles'[Dataset]) = SelectedDataset
                )
                && (
                    ISBLANK(SelectedDataOwner) || RELATED('Data_Cost_Profiles'[Data Owner]) = SelectedDataOwner
                )
                && (
                    ISBLANK(SelectedCurrentApprover) || RELATED('Data_Cost_Profiles'[Current Approver]) = SelectedCurrentApprover
                )
                && (
                    ISBLANK(SelectedCurrentVendor) || RELATED('Data_Cost_Profiles'[Vendor Name]) = SelectedCurrentVendor
                )
                && (
                    ISBLANK(SelectedCurrentVendorType) || RELATED('Data_Cost_Profiles'[Vendor Type]) = SelectedCurrentVendorType
                )
        )
    )
)
Labels:
Message 1 of 7
427 Views
0
6 REPLIES 6
Pandadev
Post Prodigy
Post Prodigy

‎11-07-2023 01:35 AM

I have added these measures 

SelectedCostGroup = SELECTEDVALUE('Data_Cost_Profiles'[Cost Group])
SelectedCostType = SELECTEDVALUE('Data_Cost_List'[Cost Type])
SelectedCurrentApprover = SELECTEDVALUE('Data_Cost_Profiles'[Current Approver])
SelectedCurrentVendor = SELECTEDVALUE('Data_Cost_Profiles'[Vendor Name])
SelectedCurrentVendorType = SELECTEDVALUE('Data_Cost_Profiles'[Vendor Type])
SelectedDataOwner = SELECTEDVALUE('Data_Cost_Profiles'[Data Owner])
SelectedDataset = SELECTEDVALUE('Data_Cost_Profiles'[Dataset])
 
Message 7 of 7
391 Views
0
123abc
Community Champion
Community Champion

‎11-07-2023 01:15 AM

Try This plz:

 

Cumulative - Cost =
VAR CurrentMonth = MAX('Data_Cost_List'[Date])
VAR TotalCost =
CALCULATE(
SUM('Data_Cost_List'[Value]),
FILTER(
ALL('Data_Cost_List'),
'Data_Cost_List'[Date] <= CurrentMonth
)
)
VAR CostByCostType =
IF (
ISFILTERED('Data_Cost_List'[Cost Type]),
CALCULATE(
SUM('Data_Cost_List'[Value]),
FILTER(
ALL('Data_Cost_List'),
'Data_Cost_List'[Date] <= CurrentMonth,
'Data_Cost_List'[Cost Type] = SelectedCostType
)
),
TotalCost
)
VAR CostByCostGroup =
IF (
ISFILTERED('Data_Cost_Profiles'[Cost Group]),
CALCULATE(
SUM('Data_Cost_List'[Value]),
FILTER(
ALL('Data_Cost_List'),
'Data_Cost_List'[Date] <= CurrentMonth,
RELATED('Data_Cost_Profiles'[Cost Group]) = SelectedCostGroup
)
),
CostByCostType
)
VAR CostByDataset =
IF (
ISFILTERED('Data_Cost_Profiles'[Dataset]),
CALCULATE(
SUM('Data_Cost_List'[Value]),
FILTER(
ALL('Data_Cost_List'),
'Data_Cost_List'[Date] <= CurrentMonth,
RELATED('Data_Cost_Profiles'[Dataset]) = SelectedDataset
)
),
CostByCostGroup
)
VAR CostByDataOwner =
IF (
ISFILTERED('Data_Cost_Profiles'[Data Owner]),
CALCULATE(
SUM('Data_Cost_List'[Value]),
FILTER(
ALL('Data_Cost_List'),
'Data_Cost_List'[Date] <= CurrentMonth,
RELATED('Data_Cost_Profiles'[Data Owner]) = SelectedDataOwner
)
),
CostByDataset
)
VAR CostByCurrentApprover =
IF (
ISFILTERED('Data_Cost_Profiles'[Current Approver]),
CALCULATE(
SUM('Data_Cost_List'[Value]),
FILTER(
ALL('Data_Cost_List'),
'Data_Cost_List'[Date] <= CurrentMonth,
RELATED('Data_Cost_Profiles'[Current Approver]) = SelectedCurrentApprover
)
),
CostByDataOwner
)
VAR CostByVendorName =
IF (
ISFILTERED('Data_Cost_Profiles'[Vendor Name]),
CALCULATE(
SUM('Data_Cost_List'[Value]),
FILTER(
ALL('Data_Cost_List'),
'Data_Cost_List'[Date] <= CurrentMonth,
RELATED('Data_Cost_Profiles'[Vendor Name]) = SelectedCurrentVendor
)
),
CostByCurrentApprover
)
VAR CostByVendorType =
IF (
ISFILTERED('Data_Cost_Profiles'[Vendor Type]),
CALCULATE(
SUM('Data_Cost_List'[Value]),
FILTER(
ALL('Data_Cost_List'),
'Data_Cost_List'[Date] <= CurrentMonth,
RELATED('Data_Cost_Profiles'[Vendor Type]) = SelectedCurrentVendorType
)
),
CostByVendorName
)
RETURN
CostByVendorType

 

This modified measure calculates the cumulative cost for each filter selection and then aggregates them to provide the overall cumulative cost. It checks for each filter if it is selected and applies the corresponding filter criteria accordingly.

 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

 

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Message 2 of 7
401 Views
0
Pandadev
Post Prodigy
Post Prodigy
In response to 123abc

‎11-07-2023 11:43 PM

Do you think this is possible all I want is to have all the selected filters applied , so the cumulative value is changed based on all filters. I spent 8 hrs trying to get it to work yesterday , but nothing I am trying seems to work. 

Message 6 of 7
364 Views
0
Pandadev
Post Prodigy
Post Prodigy
In response to 123abc

‎11-07-2023 02:03 AM

This fixed the errros by replacing , with && , but the filters are still not working , they appear to filter the months where the cost are showing , but the total cumalutive value is not being changed  , here is what i have so far based on your exmple , 

Cumulative - Cost =
VAR CurrentMonth = MAX('Data_Cost_List'[Date])
VAR TotalCost =
CALCULATE(
SUM('Data_Cost_List'[Value]),
FILTER(
ALL('Data_Cost_List'),
'Data_Cost_List'[Date] <= CurrentMonth
)
)
VAR CostByCostType =
IF (
ISFILTERED('Data_Cost_List'[Cost Type]),
CALCULATE(
SUM('Data_Cost_List'[Value]),
FILTER(
ALL('Data_Cost_List'),
'Data_Cost_List'[Date] <= CurrentMonth &&
'Data_Cost_List'[Cost Type] = [SelectedCostType]
)
),
TotalCost
)
VAR CostByCostGroup =
IF (
ISFILTERED('Data_Cost_Profiles'[Cost Group]),
CALCULATE(
SUM('Data_Cost_List'[Value]),
FILTER(
ALL('Data_Cost_List'),
'Data_Cost_List'[Date] <= CurrentMonth &&
RELATED('Data_Cost_Profiles'[Cost Group]) = [SelectedCostGroup]
)
),
CostByCostType
)
VAR CostByDataset =
IF (
ISFILTERED('Data_Cost_Profiles'[Dataset]),
CALCULATE(
SUM('Data_Cost_List'[Value]),
FILTER(
ALL('Data_Cost_List'),
'Data_Cost_List'[Date] <= CurrentMonth &&
RELATED('Data_Cost_Profiles'[Dataset]) = [SelectedDataset]
)
),
CostByCostGroup
)
VAR CostByDataOwner =
IF (
ISFILTERED('Data_Cost_Profiles'[Data Owner]),
CALCULATE(
SUM('Data_Cost_List'[Value]),
FILTER(
ALL('Data_Cost_List'),
'Data_Cost_List'[Date] <= CurrentMonth &&
RELATED('Data_Cost_Profiles'[Data Owner]) = [SelectedDataOwner]
)
),
CostByDataset
)
VAR CostByCurrentApprover =
IF (
ISFILTERED('Data_Cost_Profiles'[Current Approver]),
CALCULATE(
SUM('Data_Cost_List'[Value]),
FILTER(
ALL('Data_Cost_List'),
'Data_Cost_List'[Date] <= CurrentMonth &&
RELATED('Data_Cost_Profiles'[Current Approver]) = [SelectedCurrentApprover]
)
),
CostByDataOwner
)
VAR CostByVendorName =
IF (
ISFILTERED('Data_Cost_Profiles'[Vendor Name]),
CALCULATE(
SUM('Data_Cost_List'[Value]),
FILTER(
ALL('Data_Cost_List'),
'Data_Cost_List'[Date] <= CurrentMonth &&
RELATED('Data_Cost_Profiles'[Vendor Name]) = [SelectedCurrentVendor]
)
),
CostByCurrentApprover
)
VAR CostByVendorType =
IF (
ISFILTERED('Data_Cost_Profiles'[Vendor Type]),
CALCULATE(
SUM('Data_Cost_List'[Value]),
FILTER(
ALL('Data_Cost_List'),
'Data_Cost_List'[Date] <= CurrentMonth &&
RELATED('Data_Cost_Profiles'[Vendor Type]) = [SelectedCurrentVendorType]
)
),
CostByVendorName
)
RETURN
CostByVendorType
Message 5 of 7
387 Views
0
Pandadev
Post Prodigy
Post Prodigy
In response to 123abc

‎11-07-2023 01:23 AM

Hi thanks for looking at this for me , I tried your code , but there are errors on these lines of 

'Data_Cost_List'[Cost Type] = SelectedCostType
RELATED('Data_Cost_Profiles'[Cost Group]) = SelectedCostGroup
RELATED('Data_Cost_Profiles'[Dataset]) = SelectedDataset
RELATED('Data_Cost_Profiles'[Data Owner]) = SelectedDataOwner
RELATED('Data_Cost_Profiles'[Current Approver]) = SelectedCurrentApprover
RELATED('Data_Cost_Profiles'[Vendor Name]) = SelectedCurrentVendor , added this to see if it would fix it 
VAR SelectedCostType = SELECTEDVALUE('Data_Cost_List'[Cost Type])
VAR SelectedCostGroup = SELECTEDVALUE('Data_Cost_Profiles'[Cost Group])
VAR SelectedDataset = SELECTEDVALUE('Data_Cost_Profiles'[Dataset])
VAR SelectedDataOwner = SELECTEDVALUE('Data_Cost_Profiles'[Data Owner])
VAR SelectedCurrentApprover = SELECTEDVALUE('Data_Cost_Profiles'[Current Approver])
VAR SelectedCurrentVendor = SELECTEDVALUE('Data_Cost_Profiles'[Vendor Name])
VAR SelectedCurrentVendorType = SELECTEDVALUE('Data_Cost_Profiles'[Vendor Type])
 
but it still says enexpected parameter
Message 3 of 7
396 Views
0
Pandadev
Post Prodigy
Post Prodigy
In response to Pandadev

‎11-07-2023 02:29 AM

When I tested the code , th eonly filter that works is Vendor type , is this because the last part of the code has RETRURN 

CostByVendorType , if I try to add other filters they have no affect on the total
Message 4 of 7
383 Views
0

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All
Top Solution Authors
View All
Top Kudoed Authors
View All