#Dax Help - Slicer for multiple values column

Vishy · August 21, 2019, 12:17pm

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

Kindly request you provide your help for the same.

Paul · August 21, 2019, 2:48pm

Hi,
Could you include the pbix you created?

Paul

Vishy · August 21, 2019, 4:07pm

Hi Paul,

The pbix file has been uploaded. Thanks in advance

sample _multiple column value slicer.pbix (43.1 KB)

Paul · August 21, 2019, 6:21pm

Hi,

Why not extending the DimSimType Table with the combinations?

Vishy · August 22, 2019, 4:52am

kindly request you to elaborate on your question… just a point but there can be multiple combinations of the sales type in production so i consider that it cannot be a feasible approach

Paul · August 22, 2019, 7:26am

Hi,
I have looked at several options, but the point is that a slicer originates from a column of unique items. A and A+B are 2 different things. If you do not have too many permutations I would recommend adding them to the Simtype list. Alternative, create groups (assuming all Sim types are in the Sales- and Simtype table) .

Paul

Vishy · August 22, 2019, 8:27am

So production there are seven sim type and the permutations would too many.

Can you give me clarity on the groups part. basically note that the sales amount mentioned is a combination of A+B and we are not aware how much does individual sim type contributes.(information till that gran is not available)

Paul · August 22, 2019, 9:58am

So 49 different types possible, assuming A+B and B+A are possible combinations.
Not that many, but if you create groups of them they can be reduced.
You can create groups by right clicking on the field and select group.

Alternatively, see attached file, I have added a CONTAINSSTRING calculated column to the sales data to show records with a combination, play around with this.
sample _multiple column value slicer.pbix (66.0 KB)

Whilst typing I realise that you could also create a table (in Excel) with the possible permutations and a category or group and merge this with the sales data file.

Paul

Vishy · August 23, 2019, 10:16am

Hi Paul,

Based on the pbix shared by you i see blank values against the sales amount because of the relationship created.

Agreed with the groups logic but user wants to see A+B as well as A & B individually as well. In that case how the groups going to work.

however i have modified my formula to some extent using concatenate function which filters the data based on multiple selection if that combination exists in fact table, but doesnt search the string individually.

Sales Amount =

Var _SimType = CONCATENATEX(DimSimType,[SimType],"+")

Return

IF(NOT(ISFILTERED(DimSimType[SimType])),SUM('Sales Table'[Sales Amount]),

SUMX('Sales Table',If(FIND(_SimType,'Sales Table'[Sim Type],1,0)>0,'Sales Table'[Sales Amount],BLANK())))

With respect to creating a excel mapping , if at all in future additional values are added in dimension , in that case it would be a manual activity to maintain the table. Client basically trying to avoid manual maintenance.

Paul · August 23, 2019, 3:47pm

Hi Vishy,

Nice, I slightly misinterpreted your requirement. Many ways to solve a problem in PBI.
As far as the simtype table is concerned, you could also automate building the distinct types table in PowerQuery. Duplicate the Sales query, drilldown the type column, remove duplicates and it is done.When values are added to the sales data, the list would then be refreshed.

Paul