Getting info Outside Slicers & mix with Sliced data

[Bahalzamon]

I am attempting to get information ouside slicers using CALCULATE and CALCULATETABLE but I am unable to get anything working as I am assuming I just dont fully understand the proper implimentations. Everything I can find is constantly doing SUMs of last year finances lol

Basically I have a Service table that all my visuals are based off of that has a slicer filtering the date as well as another filtering the client.

So heres an example of one of my calculations:

Complete Count LY = CALCULATE(COUNTROWS(cyb_service),cyb_service[_Complete/Incomplete]="Complete",'Date'[Year]=YEAR(TODAY())-1)+0

So this returns information only if the visual is not effected by a slicer. I would like to have this in a visual that is effected by a slicer, like 2 bar graphs so I can compare values. I am assuming I need something like ALL or one of those things.

But I am also wondering how I make a measure and/or table that gives me information on like the Type of Service as I have a Type option ont he table and for my usual visuals I jsut drop the Type in and it seperates and counts each type. I am wondering how I go about doing that information unaffected by the slicers and into a visual with data that is effected by slicers. Preferably without having to do a special filter like the above cyb_service[_Complete/Incomplete]="Complete" as I think I would have to do one for every type.

Hopefully I have explained myself well enough, I can provide any additional info if needed, but unfortunately I am unable to share the file.

[123abc]

It seems like you're working with Power BI or a similar tool for data visualization and analysis. You're correct in identifying that you need to modify your DAX measures to work properly with slicers while also considering filtering criteria.

Let's address your two main concerns:

1. Calculating Measures Outside Slicers: To calculate measures that are not influenced by slicers, you need to use functions like ALL or ALLEXCEPT to remove the effect of slicers. For example, if you want to calculate the "Complete Count LY" regardless of slicer selections, you can use ALL to remove slicer filters:

Complete Count LY =
CALCULATE(
COUNTROWS(cyb_service),
cyb_service[_Complete/Incomplete]="Complete",
ALL('Date')
) + 0

1. The ALL('Date') removes any filters applied to the 'Date' table, essentially considering all the data from the previous year.

2. Aggregating Information by Type of Service: To aggregate information by the type of service, you can create a measure that calculates the count of rows for each type. Again, you might need to remove slicer filters to get the correct count:

Service Type Count =
CALCULATE(
COUNTROWS(cyb_service),
ALLEXCEPT(cyb_service, cyb_service[Type of Service])
)

1. This measure will count the rows in the 'cyb_service' table disregarding all slicers except for the 'Type of Service' slicer.

By using ALL and ALLEXCEPT functions appropriately, you can control how slicers affect your measures and ensure they provide the desired results even when slicers are applied in your visuals. Adjust these DAX measures as needed based on your specific requirements and data model.

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.

[Bahalzamon]

Sorry about the long time befor responding. I think that definitely helps me understand it far better. 🙂

Part of my end result I am attempting to get with this post was the ability to get a table of data back (if possible) of the Type column cyb_service[Type], I explained above but poorly. lol

I am hopeing to get kind of like a singular column that way I am able to drop it into a visual and it shows all the columns count.

_Service LY = 
CALCULATETABLE(
    cyb_service,
    ALL('Date')
)

But as cyb_service is a table and not a singular column it has an issue. I am assuming I am going about this 100% wrong. Using the formulas mentioned I would have to do a measure for each Type. Is there a way to get all the Types as if they add a new one I would have to go into the model and add the new measure and drop it into all visuals. But the visuals that I just dropped the Type from the table it will automatically update.

Hopefully I am making sense, I can provide visuals if it would help lol