Slicer for column with multiple values

[vishy0501]

Hi ALL,

@Greg_Deckler 

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()
    )

[Greg_Deckler]

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?

[vishy0501]

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

[belisqui]

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.

[SGK1228]

This is exactly what I need, BUT I also need to filter out the "Duplicate" ID values to only show a consolidated view;

I also need to remove the "Column for slicer" column in the end to show ID and key ONLY. Any ideas community?

ID    Key        Column For Slicer

1         A                  A

2         A+B             A

2         A+B             B

3          B                  B

4          C                 C

[vishy0501]

Hi Belisqui,

I had though about this approach but in production there are multiple values of sim type and in that case this approach doesnt seem to be ethically correct.

however i have reached at a stage wherein user selects any combination of the sim type and if it exists in the fact table it gives the result. but it doesnt search as individual string. the updated logic is as follows -

Campaign Group Sales converted =

VAR SuiteDescr =

   CONCATENATEX(dimsim,Dimsim[simtype]," + ")

VAR Channel =

    SELECTEDVALUE ( sales(simtype )

RETURN

IF(NOT(ISFILTERED(Dimsim[simtype])),SUM ( [sales amount] ),

    IF (

        FIND ( SuiteDescr, Channel, 1, 0 ) > 0,

        SUM ( sales[sales amount] ),

        BLANK()

    ))

@belisqui 

[amitchandak]

I tried by creating two additional DAX fields and giving inactive relations on them. There are two issues with this approach.

1. I am getting a blank value. Not able to remove that

2. It is only able to solve if you have one + symbol

First = LEFT(Sales[Column1],IFERROR( SEARCH("+",Sales[Column1],1)-1,99))
Second = right(Sales[Column1],IFERROR( SEARCH("+",Sales[Column1],1)-1,0))

Sales 2 =
VAR Column1=
SELECTEDVALUE ( dim[Column1])
VAR Column2 =
SELECTEDVALUE ( Sales[Second])
RETURN
CALCULATE( SUM (Sales[Sales]),
find( Column1,Sales[First] , 1, 0 ) > 0 ,USERELATIONSHIP(dim[Column1],Sales[First])

)+CALCULATE( SUM (Sales[Sales]),
find( Column1,Sales[Second] , 1, 0 ) > 0 ,USERELATIONSHIP(dim[Column1],Sales[Second])

)-CALCULATE( SUM (Sales[Sales]),
find( "+",Sales[Column1] , 1, 0 ) > 0 ,USERELATIONSHIP(dim[Column1],Sales[Second])

)

[vishy0501]

Hi @amitchandak  - 

I will try the method suggested by you however i am able to implement a way by spilting the column and searching in each column and checking wether it is > 0 and having this measure at a visual level filter. But only issue is spilting the column because if there is a combination of A+B+C then it doesnt create a dynamic 3rd measure for the search  -  

measures created are as below - 

inmember1_ =

Var simtype = SELECTEDVALUE(DimSimType[SimType])

Var simtypedesr = SELECTEDVALUE('Sales Table'[Sim Type.1])

Return

SUMX('Sales Table',IF(FIND(simtype,simtypedesr,1,0)>0,1,0))

inmember2_ =

Var simtype = SELECTEDVALUE(DimSimType[SimType])

Var simtypedesr = SELECTEDVALUE('Sales Table'[Sim Type.2])

Return

SUMX('Sales Table',IF(FIND(simtype,simtypedesr,1,0)>0,1,0))

InEither = IF([InMember1_]+[inmember2_]>0,1,0)

Using ineither as a visual level filter with value = 1. This helps to avoid the relationship usage as well and no blanks.

[vishy0501]

Hi Amit,

The query which you have used for 1st and 2nd is perfect similar to the spilt that we can achieve in Power Query.

However in the next steps rather than using in active relationship and getting blank we can use find and ineither query to get the desired result.

Now my only concern is how to get dyanamically the spilt without creating a new column i.e. is there any function in DAX which can search a word in a sentence and filter values accordingly. 

I want to thanks all of you for the efforts in helping me out.

[amitchandak]

One of the solution is to split the column and then try the formula

https://community.powerbi.com/t5/Desktop/Split-column-not-into-seperate-columns-but-in-rows/m-p/1317...

[vishy0501]

[vishy0501]

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