topic Re: DAX Help to Restrict Filtering Column in a table in DAX Commands and Tips

DAX Help to Restrict Filtering Column in a table

Amitkr174 — Thu, 30 Apr 2020 14:33:00 GMT

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:-

Re: DAX Help to Restrict Filtering Column in a table

Greg_Deckler — Thu, 30 Apr 2020 14:41:26 GMT

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

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

Re: DAX Help to Restrict Filtering Column in a table

camargos88 — Thu, 30 Apr 2020 14:47:29 GMT

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

Re: DAX Help to Restrict Filtering Column in a table

Amitkr174 — Thu, 30 Apr 2020 14:50:19 GMT

Hi - This is not working.

Re: DAX Help to Restrict Filtering Column in a table

Amitkr174 — Fri, 01 May 2020 13:44:40 GMT

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?

Re: DAX Help to Restrict Filtering Column in a table

camargos88 — Fri, 01 May 2020 13:52:43 GMT

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

Re: DAX Help to Restrict Filtering Column in a table

Amitkr174 — Fri, 01 May 2020 13:57:25 GMT

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:-

Re: DAX Help to Restrict Filtering Column in a table

camargos88 — Fri, 01 May 2020 14:02:20 GMT

Amitkr174 ,

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

Re: DAX Help to Restrict Filtering Column in a table

Amitkr174 — Fri, 01 May 2020 14:11:51 GMT

No we don't have that option.

We have the option to use only DAX.

Re: DAX Help to Restrict Filtering Column in a table

camargos88 — Fri, 01 May 2020 14:30:49 GMT

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

Re: DAX Help to Restrict Filtering Column in a table

Anonymous — Fri, 01 May 2020 20:04:47 GMT

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.

Re: DAX Help to Restrict Filtering Column in a table

Amitkr174 — Fri, 01 May 2020 21:46:59 GMT

Hi Vikram,

This is not working. Please see the screenshot.

Re: DAX Help to Restrict Filtering Column in a table

Anonymous — Sat, 02 May 2020 17:36:05 GMT

Hi Amit,

Don't use flying measures.

Use like below example:

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