Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Display table text only if ALL values in a group are selected

Hi, I have two data tables that I'm working with, called 1_Main_Data and 1_Text_Data. I'd like to connect 1_Main_Data and 1_Text_Data.

 

The tables looks like this:

1_Main_Data:

Value Type (group)Value TypeDateValue
RevenueRevenue Stream 112/31/2021100
RevenueRevenue Stream 13/31/2022110
RevenueRevenue Stream 16/30/2022115
RevenueRevenue Stream 212/31/2021700
RevenueRevenue Stream 23/31/2022710
RevenueRevenue Stream 26/30/2022720
RevenueRevenue Stream 312/31/20215
RevenueRevenue Stream 33/31/202220
RevenueRevenue Stream 46/30/202225
BalanceAccount 112/31/20211000
BalanceAccount 13/31/20221200
BalanceAccount 16/30/20221300
BalanceAccount 212/31/20212000
BalanceAccount 23/31/20223000
BalanceAccount 26/30/20223400

 

1_Commentary_Data:

Value Type (group)Forecast Start PeriodText
Revenue12/31/2021Stream 1 grew at 5 units per quarter
Revenue12/31/2021Stream 3 had highest percentage growth
Revenue12/31/2021No revenue Streams had negative values
Balance12/31/2021Account balances grew
Balance12/31/2021Account 2 had largest percentage growth

 

 

So, both of these tables contain data pertaining to revenues and account balance forecasts. The forecasts begin on the Forecast Period Start Date and continue until 6/30/2022. The 1_Commentary_Data table contains a text column that will be used to display some commentary in a table/matrix. 

 

I have a slicer that shows Value Type (Group) first and then Value Type. These slicer variables are coming from 1_Total_Data. What I want is for the commentary to show up when I select ALL values of a particular Value Type (Group). So, it should be blank if I only select Revenue Stream 1, but if I select Revenue Stream 1, 2, and 3, then it should show all of the Text values in 1_Commentary_Data in a table or matrix.

 

How can I do this? I don't understand how these tables could be linked in the data model, becuase Revenue or Balance is showing up on every row of Value Type (group), so any selection of a value in that group will give the table the greenlight to show the Text Values. I've also tried a measure, but that won't allow me to pass a table of values to the Table or Matrix visualizations.

 

Is there any way to do what I want? I would appreciate any insight on this problem. Thank you!

1 ACCEPTED SOLUTION
v-binbinyu-msft
Community Support
Community Support

Hi @Anonymous ,

Please try below steps

1. my test table 

Table:

vbinbinyumsft_0-1662083361578.png

Table2:

vbinbinyumsft_1-1662083380028.png

Model:

vbinbinyumsft_2-1662083423563.png

2. add two slicer visual and add two fields from "Table"

vbinbinyumsft_3-1662083486915.png

3. create a measure and add it to card visual

Measure =
VAR cur_valuetype_group =
    HASONEFILTER ( 'Table'[Value Type (group)] )
VAR cur_valuetype =
    HASONEFILTER ( 'Table'[Value Type] )
VAR tmp =
    FILTER (
        ALL ( 'Table 2' ),
        cur_valuetype_group = TRUE ()
            && cur_valuetype = FALSE ()
            && 'Table 2'[Value Type (group)] = SELECTEDVALUE ( 'Table'[Value Type (group)] )
    )
RETURN
    CONCATENATEX ( tmp, [Text] & ";", "
" )

Animation8.gif

Please refer the attached .pbix file.

 

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-binbinyu-msft
Community Support
Community Support

Hi @Anonymous ,

Please try below steps

1. my test table 

Table:

vbinbinyumsft_0-1662083361578.png

Table2:

vbinbinyumsft_1-1662083380028.png

Model:

vbinbinyumsft_2-1662083423563.png

2. add two slicer visual and add two fields from "Table"

vbinbinyumsft_3-1662083486915.png

3. create a measure and add it to card visual

Measure =
VAR cur_valuetype_group =
    HASONEFILTER ( 'Table'[Value Type (group)] )
VAR cur_valuetype =
    HASONEFILTER ( 'Table'[Value Type] )
VAR tmp =
    FILTER (
        ALL ( 'Table 2' ),
        cur_valuetype_group = TRUE ()
            && cur_valuetype = FALSE ()
            && 'Table 2'[Value Type (group)] = SELECTEDVALUE ( 'Table'[Value Type (group)] )
    )
RETURN
    CONCATENATEX ( tmp, [Text] & ";", "
" )

Animation8.gif

Please refer the attached .pbix file.

 

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors