DAX Help to Restrict Filtering Column in a table

[Amitkr174]

Hi,

I need a help on DAX, I want to filter the 'Net Available Hours' only based on the 'Resource Region' filter. The 'Billable hours' should not get filtered out. It should remain 112,462 only. Screenshot is below:-

[camargos88]

Hi @Amitkr174 ,

I believe you need a disconected table for it:

Create a new table using the region values: Region = VALUES('Table'[REGION])

Do not relate it with any table and use it as slicer.

Create your measures:

_1 =

VAR _region = VALUES(Region[REGION])

RETURN CALCULATE(SUM('Table'[VALUE 1]); 'Table'[REGION] IN _region)

_2 = CALCULATE(SUM('Table'[VALUE 2]))

Check this file: Download PBIX 

If you consider it as a solution, please mark as a solution and kudos.

Ricardo

[Amitkr174]

Thanks @camargos88  for your reply!

Issue is that I using direct query here and I don't have rights to modify a table.

Is there any other way via which this can be resolved? 

[camargos88]

@Amitkr174 ,

You don't need to change your source, just remove the relationship in Power BI.

If you want, you can duplicate the region values as table and use this example.

Did I answer your question? Mark my post as a solution!
Ricardo

[Amitkr174]

@camargos88  - Data is flowing from SSAS cube into Power bi and we cannot modify the structure of the ERD. Below is the screenshot of the different objects in the cube:-

[camargos88]

@Amitkr174 ,

Can't you try creating another query import just this region table to your model ?

[Amitkr174]

No we don't have that option.

We have the option to use only DAX.

[camargos88]

@Amitkr174 ,

I hope you have a separated table for region...try this one:

_1 = CALCULATE(SUM('Table'[VALUE 1]); CROSSFILTER('Table (2)'[REGION]; 'Table'[REGION]; None))

Ricardo

[Greg_Deckler]

So, you need to use ALLEXCEPT in that case. Something along the lines of:

CALCULATE(SUM([Billable Hours]),ALLEXCEPT('Table'[Resource Region]))

[Amitkr174]

Hi - This is not working.

[Anonymous]

Replacre it with the exact dax

Hi Amit,

It will work if you calculate the measure in the MEASURE 3 itself ,It will not work if you call it from earlier created DAX Measure.

[Amitkr174]

Hi Vikram,

This is not working. Please see the screenshot.

[Anonymous]

Hi Amit,

Don't use flying measures.

Use like below example:

MeasureName= CALCULATE(SUM(BILLING HOURS),ALLEXCEPT(Utilization,Utilization[Resource Practice]))