Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
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
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
@Anonymous , Check if treatas can help you
https://docs.microsoft.com/en-us/dax/treatas-function
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |