cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
admin11
Memorable Member
Memorable Member

Dynamic dimension only work for Brand but not Segment ?

Hi All

 

(1) Procedure to create a dynamic dimension table.

Step 1 Create a SliceTable with value label = Brand . Segment for user selection purpose.

admin11_0-1642718810717.png

Step 2 Create a measure for display Dimension value label.

Step 3 Create a measure for display the sales amount.

Step 4 create a Summarize table with rank field for Brand.

admin11_1-1642718918460.png

 

Step 5 Create a Summarise table with rank field for Segment.

admin11_2-1642718974244.png

Step 6 link the Step 4 and 5 table by Rank.

 

admin11_3-1642719191251.png

(2) Working PBI file create by @v-yangliu-msft :-

https://www.dropbox.com/s/f1hdxxe486o803s/WORKING.pbix?dl=0

 

I have create a totally new PBI file from import raw data and follow the above step by step , and i still unable to get it work. My issue is i can set when user select Brand . Table 1 can display sales by Brand , But when user select Segment , it cannot display display sales by segment correctly as every row same value.

 

(3) Not working PBI file create by me :-

https://www.dropbox.com/s/g75b3te1tldthvv/NOT%20WORKING.pbix?dl=0

 

Paul Yeo

 

Hope some one can share with me why PBI file create by me not able to work ?

 

Thank

 

Paul

3 REPLIES 3
admin11
Memorable Member
Memorable Member

Hi All

 

@v-yangliu-msft have share with me below post on step by step for create dynamic dimension :- 

 

https://community.powerbi.com/t5/Desktop/How-to-make-use-of-one-table-to-replace-3-table/td-p/225667...

 

In order to undertand , i have create a new PBI file follow above every step by step. 

 

(1) When Slice= BRAND , Table can display Brand info is correct :-

admin11_0-1642586887753.png

(2) My issue is when i select Sliec = SEG_SUB , Table 1 2nd column not display data.

admin11_1-1642587032147.png

 

Hope some one can share with me where go wrong ?

 

My PBI sample file :-

https://www.dropbox.com/s/hulunsd3zjn67qg/SAMPLE_1000_SI_TI_V012.pbix?dl=0

 

Paul Yeo

v-easonf-msft
Community Support
Community Support

Hi, @admin11 

Measure 'S_amount_TOTAL' is not complete. The description of  value “SEG_SUB” is misssing in function 'Switch'.

Please modified it as below:

Screenshot 2022-01-24 150143.png

 

Result:

result.png

Best Regards,
Community Support Team _ Eason

@v-easonf-msft 

Thank you very much for your help. 

I have check the attach file , i notice that s_amount_TOTAL2 you already have the below full expression  , But the Table 1 still not giving correct result when i click on SEG_SUB button ?

 

S_amount_TOTAL2 =
SWITCH (
SELECTEDVALUE ( SliceTable[Slice] ),
"BRAND_C",
IF (
ISINSCOPE ( s_ummrizetable1[rank] ),
CALCULATE (
SUM ( 'SALES'[sales] ),
FILTER (
ALLSELECTED ( 'SALES' ),
[BRAND_C] = MAX ( 's_ummrizeTable1'[BRAND_C] )
)
),
SUM ( 'SALES'[sales] )
),
"COMPANY",
IF (
ISINSCOPE ( s_ummrizetable2[rank] ),
CALCULATE (
SUM ( 'SALES'[sales] ),
FILTER (
ALLSELECTED ( 'SALES' ),
[COMPANY] = MAX ( 's_ummrizeTable2'[COMPANY] )
)
),
SUM ( 'SALES'[sales] )
),
"SEG_SUB",
IF (
ISINSCOPE ( s_ummrizetable3[rank] ),
CALCULATE (
SUM ( 'SALES'[sales] ),
FILTER (
ALLSELECTED ( 'SALES' ),
[SEG_SUB] = MAX ( 's_ummrizeTable3'[SEG_SUB] )
)
),
SUM ( 'SALES'[sales] )
)
)

 

Hope you can resend the working PBI file . so that i can see that table can display amount when i select brand and seg_sub

 

Paul

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!