How to filter on Child levels (Dimensional) based on Parent level value?

[itsmebvk]

 Hi Folks,

I need small help in dimensional function. I am trying to crate a Matrix with following requirement using a hierarchy I have.

Attched Sample Data and PBI FIle

My requirement is when my Level3 =A3 I want to filter my Level3, Leve4, Level5 columns for only Location number 1 and 2, all other levels values should display as is.

For Eg : As highlighted below I created a sample measure when Level3 =A3 I want to filter all Level3=A3 and its child levels to Location number 1 and 2

Level1	Level2	Level3	Level4	Level5	Sales
ORG	A	A1	A11	Milk	100
ORG	A	A2	A21	Rice	120
ORG	A	A2	A21	Bread	25
ORG	A	A3	A31	Orange	80
ORG	A	A3	A32	Apple	140
ORG	A	A3	A31	Beer	30
ORG	A	A1	A11	Mango	11
ORG	A	A1	A11	Grapes	45
ORG	A	A1	A11	Avocado	66
ORG	A	A2	A22	Medicine	41

I tried to use Path function but it only allows 2 columns to generate path, but as I have multiple columns in  hierarchy I am unable to use it,

Can you please suggest any workarounds or inputs to crack this requirement? 

Thanks in advance.

[Fowmy]

@itsmebvk 

Where is the location information as per your source table, you have level 1 to 5 and the sales. What you do mean by location here?
Please provide the expected result as well.

Hope you need a measure.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[itsmebvk]

@Fowmy  Thanks for your reply.

I have a table called location from there I will take Location number and I will take levels from Hierarchy table. And I want to create a measure by filtering on that Location.

My current rows are in column B4-B23 rows, I have highlighted my expected result in column C4-C23 rows. If you observe C18-C23 rows it is in red color and filtered using Location number.

Thank you.

[Fowmy]

@itsmebvk 

What is the relationship between the hierarchy and the location table, provide sample data of the location as well.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[itsmebvk]

@Fowmy @amitchandak the relation between Hierarchy and Sales by Location tables is 1:N.

Can you please look at the attched sample data file and desired output.

Thank you.

[itsmebvk]

Hi Folks,

Can someone please share your thoughts or workarounds to this requirement.

Thanks in advance.

Regards

BVK

[itsmebvk]

@Greg_Deckler 

Hi Folks,

Finally I found a workaround to get close to this requirement, but I see small issue in it. I used following expression to filter Level3 and below Levels because of that levels above Leve3 and the totals are not updating. 

Test = IF(SELECTEDVALUE('Levels Table'[Level3])="A3",CALCULATE(SUM('Sales by Location'[Sales]),FILTER('Sales by Location','Sales by Location'[Location] IN {1,2})),SUM('Sales by Location'[Sales]))

Can you suggest me any corrections to get correct totals? Please check the attached sample file.

Thank you.

[itsmebvk]

Attaching Sample Data and PBI file, if possible please check the issue in PBI file.

[itsmebvk · Accepted solution]

Finally I found solution to my issue. I created two measures with following expressions

Test = IF(HASONEVALUE('Levels Table'[Level3]) && SELECTEDVALUE('Levels Table'[Level3])="A3",CALCULATE(SUM('Sales by Location'[Sales]),FILTER('Sales by Location','Sales by Location'[Location] IN {1,2})),IF(HASONEVALUE('Levels Table'[Level3]),SUM('Sales by Location'[Sales])))

Test 2 = SUMX(VALUES('Levels Table'),[Test])

And used second measure in Matrix...bingo it worked .