Forum Discussion
Slicer for column with multiple values
Hi ALL,
Kindly request you all to help me with this scenario -
Hi All,
So the requirement is as follows -
Dim table value
Column 1
A
B
C
Fact Table
Sales Column2
10 A+B
20 A
30 C
When user selects A then both row 1 and 2 should be displayed . Also when the user selects A & B both 1 and 2 rows should be displayed.
Now i have written a DAX which fulfills my 1st requirement i.e. working fine in single selection, but fails in multiple selection.
Dax is as follows -
Sales =
VAR Column1=
SELECTEDVALUE ( DimTable[Column1])
VAR Column2 =
SELECTEDVALUE ( Salestable[Column2])
RETURN
IF (
find( Column1,Column2 , 1, 0 ) > 0,
SUM (salestable[Sales]),
BLANK()
)
11 Replies
One of the solution is to split the column and then try the formula
Hi ,
I am using table visualization and the user wants to see the same number of rows i.e. A+B as a value in the column , if i spilt and follow the steps i would get additional rows for B as well. In that case how the sales amount would get spilted because we dont have sales amount at individual column 1 gran. I am attaching screen shot for reference as well for what user is expecting
I am thinking that you likely need to use VALUES to get both values in the event of a multi-selection scenario. After that perhaps something like a CROSSJOIN? Perhaps do a COUNTROWS of the CROSSJOIN and if not blank return a 1, filter the visual with that measure returning a 1?
Hello Greg_Deckler
kindly request you to provide extended help if possible on the changes you suggested. However i am not getting it right as of now:(
Cross join = IF(NOT(ISBLANK(COUNTROWS(CROSSJOIN(DimSimType,'Sales Table')))),1)Regards
Vishy
Can I suggest creating a manual table in the module with the values below then you create a many to many relationship to the fact table.
You link the key to the fact table, and use the second column in your slicer.
Key Column For Slicer
A A
A+B A
A+B B
B B
C C
Let me know if this works.
