Creating SAC Hierarchies in PowerBI, How to use SELECTEDVALUE for matrix visual( 2 hierarchies)

[sam_gift]

Hi, I am struggling in finding the solution for a visual. The visual is a matrix.
we are trying to recreate the SAP stories in Power BI.
I have one fact and one dimension table.
The dimension table has the hierarchies this is watch made me confused.
Dimension Table:

| rep_item_dim_id | rep_item_id | parent_item_id |
---------------------------------------------------
| 1 | ALL_MEASURES | <root> |
| 2 | KF.999 | <root> |
| 3 | KF.001 | ALL_MEASURES |
| 4 | KF.002 | ALL_MEASURES |
| 5 | KF.003 | ALL_MEASURES |
| 6 | KF.003.01 | KF.003 |
| 7 | KF.003.02 | KF.003 |
| 8 | KF.002.01 | KF.002 |
| 9 | KF.002.02 | KF.002 |
| 10 | KF.004 | ALL_MEASURES |
| 11 | KF.005 | ALL_MEASURES |
| 12 | KF.006 | <root>
Fact Table :
| currency | rep_item_dim_id |
-------------------------------
| 23 | 9 |
| 67 | 9 |
| 34 | 8 |
| 45 | 8 |
| 68 | 8 |
| 20 | 4 |
| 59 | 4 |
| 80 | 3 |
| 99 | 3 |
The fact table doesn't have data for all the rep_item_ids, whereas, below are the measures that I have to create in Power BI
KF.003 = KF.003.01 + KF.003.02
KF.004=KF003/KF002 i.e., (sum of currencies of rep_item_id=5)/(sum of currencies of rep_item_id =4)
KF.005=KF001*KF002
KF006= if KF003>0 then 1-(KF003/KF001) else 0.
I get that I can use SELECTEDVALUE and display the measure if they are in the same hierarchy.
What to do when the hierarchies are different. Please advise.

[123abc]

when you have multiple hierarchies in your dimension table and you want to create measures that involve items from different hierarchies, you can use DAX (Data Analysis Expressions) to define those measures. The key is to use DAX functions like SUMX, FILTER, and RELATED to work with data across different hierarchies. Here's how you can create the measures you mentioned:

KF.003 = KF.003.01 + KF.003.02: You can use the SUMX function to sum the values within a hierarchy. Create a measure like this:

KF.003 =
SUMX(
FILTER(
DimensionTable,
rep_item_id = "KF.003.01" || rep_item_id = "KF.003.02"
),
RELATED(FactTable[currency])
)

KF.004 = KF.003 / KF.002: This measure involves items from different hierarchies, so you'll need to use RELATED to establish the relationship. Create the measure like this:

KF.004 =
DIVIDE(
SUMX(
FILTER(
DimensionTable,
rep_item_id = "KF.003"
),
RELATED(FactTable[currency])
),
SUMX(
FILTER(
DimensionTable,
rep_item_id = "KF.002"
),
RELATED(FactTable[currency])
)
)

KF.005 = KF.001 * KF.002: This measure can be straightforward:

 KF.005 =
SUMX(
FILTER(
DimensionTable,
rep_item_id = "KF.001" || rep_item_id = "KF.002"
),
RELATED(FactTable[currency])
)

KF.006 = IF KF.003 > 0 THEN 1 - (KF.003 / KF.001) ELSE 0: Create this measure like this:

KF.006 =
IF(
SUMX(
FILTER(
DimensionTable,
rep_item_id = "KF.003"
),
RELATED(FactTable[currency])
) > 0,
1 - (DIVIDE(
SUMX(
FILTER(
DimensionTable,
rep_item_id = "KF.003"
),
RELATED(FactTable[currency])
),
SUMX(
FILTER(
DimensionTable,
rep_item_id = "KF.001"
),
RELATED(FactTable[currency])
)
)),
0
)

These DAX measures should allow you to calculate the desired metrics even when the hierarchies are different. Make sure to replace the hard-coded rep_item_id values with references to your dimension table as needed, and adjust the measures according to your specific table and column names.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[sam_gift]

Hi, I did create individual measures. also created one more measure which calculates these measures based on the dimension value. For example: 
Currency = SWITCH(
TRUE(),
SELECTEDVALUE('dim_rep_items'[rep_item_id]) = "KF.001",[KF.001],
SELECTEDVALUE(dim_rep_items[rep_item_id]) = "KF.003",[KF003],
SELECTEDVALUE(dim_rep_items[rep_item_id]) = "KF.002",[KF.002],
SELECTEDVALUE(dim_rep_items[rep_item_id]) = "KF.004",[KF004])

The problem I am facing is, I need to apply the measures for two levels of the matrix visual.
i.e., at "ALL_MEASURES" Level and "KF.001" Level too(referring to the below screenshot)

How can I use SelectedValue or any similar function of the dax to work this way.

[123abc]

If you want to apply your measures at two levels of the matrix visual, such as "ALL_MEASURES" and "KF.001," you can use the HASONEVALUE DAX function in combination with your SELECTEDVALUE approach to determine the level of the hierarchy and apply the measures accordingly.

Here's how you can modify your Currency measure to work at both "ALL_MEASURES" and "KF.001" levels:

Currency =
VAR CurrentRepItem = SELECTEDVALUE('dim_rep_items'[rep_item_id])
RETURN
SWITCH(
TRUE(),
CurrentRepItem = "KF.001", [KF.001],
CurrentRepItem = "KF.003", [KF.003],
CurrentRepItem = "KF.002", [KF.002],
CurrentRepItem = "KF.004", [KF.004],
HASONEVALUE('dim_rep_items'[rep_item_id]) && CurrentRepItem = "ALL_MEASURES", [KF.001],
BLANK()
)

In this modified measure:

1. CurrentRepItem stores the selected rep_item_id.

2. HASONEVALUE checks if there is only one value in the 'dim_rep_items'[rep_item_id] hierarchy, indicating that you are at a specific level. If there's only one value, it means you are at the "KF.001" level or "ALL_MEASURES" level.

3. If you are at the "ALL_MEASURES" level, it returns the [KF.001] measure. If you are at a different level, it uses the SWITCH function to apply the corresponding measure based on CurrentRepItem.

With this modified measure, it should work for both levels of the matrix visual as intended. Make sure to place the "Currency" measure in your matrix visual, and it will adapt to the level of hierarchy you are viewing.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.