Measures on the hierarchy display incorrectly

[SamVH12]

Hi guys,

I have a fact table for total gross sales and a Hierarchy table, "Hier1", with diferrent levels

The Hier1 table looks like this: 

Hier1	Hier2	Hier3	Hier4	ID	LEVEL
L1	L1-GG	L1-GG/A	L1-GG/A1	2034	4
L1	L1-GG	L1-GG/A	L1-GG/A2	2040	4
L1	L1-GG	L1-GG/B		2042	3

The total gross sales table uses Direct Query, and the Hier1 table uses Dual mode

These two tables map based on Hierarchy ID.

I created a Matrix as follows:

And I have a couple of questions:

1. I'm not sure why, when I create TGS%, it does not show the result for the child at level 4, even though the each formula inside includes a value

The formula is:

TGS% = IF(

    NOT(ISBLANK([TGS CY]))

    &&

    NOT(ISBLANK([TGS CY Ex]))

        ,(([TGS CY]/[TGS CY Ex]))/100

        ,BLANK())

2. Since the hierarchy does not have consistent levels (some have 4 levels, while others have 3), I do not want to show the rows with blank names at level 4, which I marked in blue.

To solve this, i created 2 measure, MaxLevel and LevelDisplay and added them to the Hier1_CY measure, which will replace the "TGS CY" (the TGS CY Ex will also use the same new measure as Hier1_CY) to hide blank hierarchy levels.

** MaxLevel = MAX(Hier1[level])

**LevelDisplay = ISINSCOPE( Hier1[Hier1] )

    + ISINSCOPE ( Hier1[Hier2] )

    + ISINSCOPE(Hier1[Hier3])

    + ISINSCOPE(Hier1[Hier4])

**Hier1_CY =

VAR Val = [TGS CY]

VAR Check =

    [LevelDisplay] <= [MaxLevel]

VAR Result =

    IF ( Check, Val )

RETURN

    Result

 However, the result does not show what I expected, it's only show data of level1,2,3:

 When i checked each measure, the the measure in Hier4 split into 2 rows with the same name. As a result, "Hier1_CY" does not show what i want

 

I tested the same measures in Import mode, and it worked well. However, since I need to use Direct Query and Dual mode, I am wondering if the issue is due to limitations of Direct Query mode.

Can anyone help? I would greatly appreciate it!

[Anonymous]

Hi @SamVH12 ,

ISINSCOPE does have some limitations in Direct Query mode. Please check if you are using it in a calculated column or if there is RLS in your report.

https://learn.microsoft.com/en-us/dax/isinscope-function-dax#remarks 

In addition, the information you provided is too little, the sample data is incomplete, and I cannot tell how many tables you used and the relationship between the tables. Please provide more detailed sample data and the expected results based on the sample data, thank you!

Best Regards,
Dino Tao

[SamVH12]

Hi @Anonymous ,

thank you for your reply,

I only have two tables. The first is the Hierarchy table, as I mentioned in the first post.

The second is the TGS table, which has 3 columns: Hierarchy ID, TGS CY, TGS CY Ex

Both tables are mapped based on the Hierarchy ID.

The question 1, regarding the TGS % column in the Matrix, it should calculate for the hierarchy levels L1-GG/A1, LL1-GG/A2, L1-GG/B as the formula i wrote, but it show blank in the image below.

The question 2, i created ISINSCOPE as a measure. I believe it is different from a calculated column, am I right? Additionally, I don’t have any RLS in my report.

Basically, i switched to the import mode now, and it doesnt have any issues, I just want to understand why this issue might happen.