Measure column that removes one specific slicer not working

Resolved · Low Priority · Version 365

Mon 30 Sep 2024, 09:59 Edited on Mon 30 Sep 2024, 18:23 Delegate Grazi said...

Grazi has attended:
Power BI Modelling, Visualisation and Publishing course

Measure column that removes one specific slicer not working

hello!

I tried to solve this, but I'm not managing and I'd love some help.

I connect to data straight via SQL Server, so it's live data. The schema is not ideal so I change it slightly to make it work once it loads.

In summary, my issue is with the following tables:
- cases table: case ID, client ID, project ID, open date, closed date
- clients table: client ID, gender
- projects table: project ID, project name
- probject_client table: project ID, client ID

As shown above, cases and clients are 1:M (1 client can have many cases, 1 case has 1 client); projects and cases are 1:1; clients and projects are M:M.

Then I have a table where I have the breakdown (gender, distinct client ID, distinct client and project IDs). Both counts are measures, they count the distinct client IDs within the date range; and the distinct client and project IDs within the date range.

As well as the date slicer, I have a project name drop-down / slicer.

The problem is that I want both those columns to ignore the project name slicer. I tried ALL,REMOVEFILTERS, but it never works. I think the relationships are too complicated or something.

Any ideas?

Thank you! Grazi

Measure that counts the clients:
CountClient inc blank = CALCULATE(DISTINCTCOUNT(' cases'[Client_ID]),union(filter(all(CloseDateTable),CloseDateTable[Date]=blank()),CloseDateTable))

I add:
ALL('cases'),ALL('clients') OR
REMOVEFILTER and none work

Mon 30 Sep 2024, 16:30 Trainer Jens said...

RE: Measure column that removes one specific slicer not working

Hi Grazi!

Thank you for the forum question.

To ensure your measures ignore the project name slicer, you can use the ALL or REMOVEFILTERS functions correctly within your CALCULATE function. Here’s a refined approach:

Measure to Count Clients Ignoring Project Name Slicer
CountClient inc blank =
CALCULATE(
DISTINCTCOUNT('key2crm_praxis cases'[Client_ID]),
UNION(
FILTER(ALL(CloseDateTable), CloseDateTable[Date] = BLANK()),
CloseDateTable
),
REMOVEFILTERS('key2crm_praxis projects_clients'[Project_ID])
)

Measure to Count Clients and Projects Ignoring Project Name Slicer
CountClientProject inc blank =
CALCULATE(
DISTINCTCOUNT('key2crm_praxis projects_clients'[Client_ID]),
UNION(
FILTER(ALL(CloseDateTable), CloseDateTable[Date] = BLANK()),
CloseDateTable
),
REMOVEFILTERS('key2crm_praxis projects_clients'[Project_ID])
)

Explanation:
REMOVEFILTERS: This function removes filters from the specified columns or tables. In this case, it removes filters from the Project_ID column in the projects_clients table.
ALL: This function returns all rows in a table or all values in a column, ignoring any filters that might have been applied.
By using REMOVEFILTERS on the Project_ID column, you ensure that the project name slicer does not affect your measures.

Give this a try and let me know if it works for you! If you encounter any issues or need further adjustments, feel free to ask.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Mon 30 Sep 2024, 22:49 Delegate Grazi said...

RE: Measure column that removes one specific slicer not working

Hi Jens, thanks for the quick reply.

I tried both ALL and REMOVEFILTERS.

I have a table which has:

gender (stored on clients table)
The DAX formula above that counts the cases (distinct clients within the date range or if closed date is blank).

When I put the REMOVEFILTERS it removes some of the count, but not all. I think because of the complexity of the relationships, it just doesn't work. I tried everything. Removing from all tables, some tables, removing only with the project ID, etc. But it messes up the counts on all instances. The closer count was using removefilters from cases table (project ID) and projects table (project ID). Still, it had 1466 instead of 2416.

Any ideas? Thank you!

Thank you,

Grazi

Fri 4 Oct 2024, 10:13 Trainer Jens said...

RE: Measure column that removes one specific slicer not working

Hi Grazi,

It sounds like the complexity of the relationships is indeed causing issues. Let’s try a different approach by using the ALLSELECTED function, which can sometimes handle complex filtering scenarios better. This function will consider all the filters except the one you explicitly remove.

Measure to Count Clients Ignoring Project Name Slicer
CountClient inc blank =
CALCULATE(
DISTINCTCOUNT('cases'[Client_ID]),
UNION(
FILTER(ALL(CloseDateTable), CloseDateTable[Date] = BLANK()),
CloseDateTable
),
REMOVEFILTERS('projects'[Project_Name]),
ALLSELECTED('cases'),
ALLSELECTED('clients')
)

Measure to Count Clients and Projects Ignoring Project Name Slicer
CountClientProject inc blank =
CALCULATE(
DISTINCTCOUNT('cases'[Client_ID] & 'cases'[Project_ID]),
UNION(
FILTER(ALL(CloseDateTable), CloseDateTable[Date] = BLANK()),
CloseDateTable
),
REMOVEFILTERS('projects'[Project_Name]),
ALLSELECTED('cases'),
ALLSELECTED('clients')
)

Explanation:
ALLSELECTED(‘cases’) and ALLSELECTED(‘clients’): These ensure that all other filters are respected except the project name slicer.
REMOVEFILTERS(‘projects’[Project_Name]): This part ensures that the project name slicer is ignored.
Additional Steps:
Check for Hidden Filters: Sometimes, hidden filters or slicers can affect the results. Ensure there are no additional filters applied.
Simplify Relationships: If possible, try to simplify the relationships in your data model to see if that resolves the issue.
Debugging: Create a simple table visual with just the client ID and project ID to see if the counts match your expectations without any slicers applied.
Give these measures a try and let me know if they bring you closer to the correct counts. If not, we can further refine the approach.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Thu 17 Oct 2024, 16:34 Delegate Grazi said...

RE: Measure column that removes one specific slicer not working

Thank you Jens.

I'm still struggling with this. So I started from scratch by replicating my original .pbix file - on 'transform data' I had some filtering as they want to always remove some records from projects and clients. I removed those filters.

Then I did the formula with the removefilter from projects only. I get the same numbers. But as soon as I filter by one project, they have different numbers, but the 2nd column doesn't have the totals. Is there a way I can send you screenshots? It'd be easier.

When I add the ALLSELECTED the numbers look even more strange.

It's a mystery! Let me know if I can send you screenshot and I'll send the DB diagram as well as the DAX for each column plus the results on the gender table.

Unfortuantely I can't send the full file as it connects to their DB.

Thank you!

Grazi

Thu 17 Oct 2024, 17:15 Delegate Grazi said...

RE: Measure column that removes one specific slicer not working

I did a table to debug. The DAX below is the closest I can get the results, but weirdly, it removes the filters where the closed date is not blank unless it's the project selected.

So I selected on my slice project ID 151, I get all the count for the project ID 151 PLUS all the count for the cases where the closed date is blank. That's why the number is higher than the selected, but still not what it should be without any filter.

I try to give an example below. Thank you so much!!

Without filter:
Gender - Count1 - Count2 (remove filter)
Not say - 4 - 4

With filter project ID = 151
Gender - Count1 - Count2 (remove filter)
Not say - 0 - 2 (it should be 4)

I checked the columns with higher numbers (eg: female) and it's the same issue. No idea why. Adding the allselected, brings lots of unfiltered records even with genders that aren't on the original table.

Count1 formula:
CountClient inc blank = CALCULATE(DISTINCTCOUNT('cases'[Client_ID]),union(filter(all(CloseDateTable),CloseDateTable[Date]=blank()),CloseDateTable))

Count2 formula - the ones that shouldn't be filtered, but it's getting filtered by the date closed not blank:
CountClient inc blank2 =
CALCULATE(
DISTINCTCOUNT('cases'[Client_ID]),
UNION(
FILTER(ALL(CloseDateTable), CloseDateTable[Date] = BLANK()),
CloseDateTable
),
REMOVEFILTERS ('projects'),
REMOVEFILTERS ('cases'[Project_ID] ),
REMOVEFILTERS('projects_clients'[Project_ID]))