Forum Discussion
Direct and Inverse Selector
Hello, I have a table like this: Department Product Category Sports AA 00001 Education BB 00002 Health AB ...
Right, so create a disconnected table like this:
Table6 = ALL('Table5'[Product])Use this for your slicer. Then, create these two measures and use them in your tables.
Normal Measure = VAR __dept = MAX([Department]) VAR __product = MAX('Table6'[Product]) VAR __table1 = SELECTCOLUMNS(FILTER(ALL('Table5'),[Product] = __product),"__dept",[Department]) RETURN IF(__dept IN __table1,1,BLANK()) Inverse Measure = VAR __dept = MAX([Department]) VAR __product = MAX('Table6'[Product]) VAR __table1 = SELECTCOLUMNS(FILTER(ALL('Table5'),[Product] = __product),"__dept",[Department]) RETURN IF(__dept IN __table1,BLANK(),1)See Table5, Table6 and Page 3 of attached.
Right, so create a disconnected table like this:
Table6 = ALL('Table5'[Product])
Use this for your slicer. Then, create these two measures and use them in your tables.
Normal Measure =
VAR __dept = MAX([Department])
VAR __product = MAX('Table6'[Product])
VAR __table1 = SELECTCOLUMNS(FILTER(ALL('Table5'),[Product] = __product),"__dept",[Department])
RETURN
IF(__dept IN __table1,1,BLANK())
Inverse Measure =
VAR __dept = MAX([Department])
VAR __product = MAX('Table6'[Product])
VAR __table1 = SELECTCOLUMNS(FILTER(ALL('Table5'),[Product] = __product),"__dept",[Department])
RETURN
IF(__dept IN __table1,BLANK(),1)
See Table5, Table6 and Page 3 of attached.
Greg,
It's working perfectly.
Thank you so much....
I think I'll submit this as a new Quick Measure!