topic Re: ISINSCOPE & SELECTEDVALUE for 2-level hierarchies (2 columns) in DAX Commands and Tips

ISINSCOPE & SELECTEDVALUE for 2-level hierarchies (2 columns)

crln-blue — Wed, 16 Dec 2020 18:12:26 GMT

Hello!

I made a fact table (summarized) using DAX with the ff columns:

Categories	Item ID	Parent ID	Dates	Amount	Path	Level 1	Level 2
A	1			355	1	A
B	2			678	2	B
C	3	2		57457	3 \| 2	B	C
D	4	2		4575	4 \| 2	B	D
E	5			458	5	E
F	6			36734	6	F
G	7	6		364	7 \| 6	F	G
H	8			464	8	H

Now, I created a single measure for all of the computations of the categories.
I used SELECTEDVALUE on a SWITCH statement:

Measure = SWITCH(SELECTEDVALUE(Level 2), get the each child values

My concern is, how about the parents' values? My parent categories has a different formulas for each (not sum, more on subtracting). For example

A = F - B

H = E - F

But upon searching I stumbled upon ISINSCOPE where the calculations will be computed per column level. (Use IsInScope to get the right hierarchy level in DAX - Kasper On BI)

Is there a way to use SELECTEDVALUE for two columns in a single measure?
Or for ISINSCOPE, can I filted the column for each value? For example:
ISINSCOPE(level2) = A

(Not sure if I should post it here or on Desktop category)

Edited: sorry for the confusion. My current problem was SELECTEDVALUE but I was also thinking about using ISINSCOPE and how would I do it. Thanks again

Re: ISINSCOPE & SELECTEDVALUE for 2-level hierarchies (2 columns)

PaulDBrown — Wed, 16 Dec 2020 17:39:34 GMT

crln-blue

Hi there. Apologies since I'm slightly confused as to what the final outcome should be. Can you post an example of the visual you wish to create including this new measure ?

Re: ISINSCOPE & SELECTEDVALUE for 2-level hierarchies (2 columns)

crln-blue — Wed, 16 Dec 2020 18:18:18 GMT

Hello PaulDBrown ,

Thanks for the question, I read it again and you're right. It's quiet confusing. I edited my post. My real concerns are the ff: using ISINSCOPE but the column is filtered and using SELECTEDVALUE in two tables.

Based on my research, seems like ISINSCOPE is the best way but another problem will arise if I segregate my data levels into many level columns - sorting of my hierarchy (a bit off topic). So I was hoping if I can use ISINSCOPE but the column is filtered to a value I desire (for computation purposes).

Currently, I'm also checking SELECTEDVALUE but when I add on the formulas, it doesn't seem to do so that's why it's blank on the matrix.

Here's a sample visual:

Re: ISINSCOPE & SELECTEDVALUE for 2-level hierarchies (2 columns)

PaulDBrown — Thu, 17 Dec 2020 16:47:13 GMT

crln-blue

Apologies again, but I am still struggling to understand what the final output should be. Could you mockup up a sample in Excel showing the original table, the calculation and the final output expected?

Re: ISINSCOPE & SELECTEDVALUE for 2-level hierarchies (2 columns)

crln-blue — Thu, 17 Dec 2020 19:45:49 GMT

Hello PaulDBrown ! I apologize for the confusion. Here's my sample file from Github.

Below is my data:

My visual:

I don;t know the right approach of this. I have parent categories where its formula is a subtraction formula. But SELECTEDVALUE only adds up using the SUM function or SUMX.

Thanks for the help!

Re: ISINSCOPE & SELECTEDVALUE for 2-level hierarchies (2 columns)

PaulDBrown — Fri, 18 Dec 2020 12:11:01 GMT

crln-blue

Thanks for the sample PBIX. It makes things much easier.

See if this is what you need:

I've attached the PBIX file for you

Re: ISINSCOPE & SELECTEDVALUE for 2-level hierarchies (2 columns)

crln-blue — Fri, 18 Dec 2020 13:40:14 GMT

Hello PaulDBrown ! Thank you very much!
I'm currently checking it now and currently studying it (will get back to you for more questions).

Anyhow, I'm curious to this:

In the Model View, it seems like a table:

But its logo under the Fields menu of the Data View is not a table. It's my first time seeing this (still a bit new to PowerBI). What does it call and how do you do it?

Thanks again for all the help!