Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

New table overview present data using slicer

Hi!

 

Hopefully someone can help me with the following question.

 

In my dashboard I use a slicer with different attributes from Table 1. I want to know whether or not these attributes are present in other tables. If they are, I would like to receive a "yes" or "true" or "1", etc. for that specific table.

 

To visualize:

 

Slicer table 1:

- attribute A

- attribute B

- attribute C

x attribute D

- attribute E

- attribute F

 

Table 2 | yes

Table 3 | no

Table 4 | yes

 

>> in this example Attribute D is present in Table 2 and 4 but not in 3. However, in the current Tables 2 & 4 there is a column containing the value "Attribute D" and not a "yes".

>> if I decide to click on Attribute C (instead of D) the "yes" and "no" values should (or could) change.

 

Is this possible to create?

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    HI Anonymous,

    I'd like to suggest you enter to 'query editor' to add a blank table that collection other queries' name and structure, then you can simply write a measure formula to compare current table name and attribute names.

    let
    Source = #table(type table[#"Table Name"=text,#"Column Name"=list],{{"Table1",Table.ColumnNames(Table1)}},{"Table2",Table.ColumnNames(Table2)}},{"Table3",Table.ColumnNames(Table3)}},{"Table4",Table.ColumnNames(Table4)}},
{"Table5",Table.ColumnNames(Table5)}}),
    #"Expanded column name" = Table.ExpandListColumn(Source, "Column Name")
in
    #"Expanded column name"

    Measure formula:

    Measure =
VAR attributeList =
    CALCULATETABLE (
        VALUES ( 'Collection'[Column Name] ),
        ALLSELECTED ( 'Collection' ),
        VALUES ( 'Collection'[Table Name] )
    )
RETURN
    IF ( SELECTEDVALUE ( 'Selector'[Attribute] ) IN attributeList, "Y", "N" )

    Notice: Collection means the table that stored table name and column name, selector means table that used as the source of slicer.

    Regards,

    Xiaoxin Sheng

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI Anonymous,

    I'd like to suggest you enter to 'query editor' to add a blank table that collection other queries' name and structure, then you can simply write a measure formula to compare current table name and attribute names.

    let
    Source = #table(type table[#"Table Name"=text,#"Column Name"=list],{{"Table1",Table.ColumnNames(Table1)}},{"Table2",Table.ColumnNames(Table2)}},{"Table3",Table.ColumnNames(Table3)}},{"Table4",Table.ColumnNames(Table4)}},
{"Table5",Table.ColumnNames(Table5)}}),
    #"Expanded column name" = Table.ExpandListColumn(Source, "Column Name")
in
    #"Expanded column name"

    Measure formula:

    Measure =
VAR attributeList =
    CALCULATETABLE (
        VALUES ( 'Collection'[Column Name] ),
        ALLSELECTED ( 'Collection' ),
        VALUES ( 'Collection'[Table Name] )
    )
RETURN
    IF ( SELECTEDVALUE ( 'Selector'[Attribute] ) IN attributeList, "Y", "N" )

    Notice: Collection means the table that stored table name and column name, selector means table that used as the source of slicer.

    Regards,

    Xiaoxin Sheng