DAX forumula help with comma separated values

[ppgandhi11]

I have a field that has comma separated value list.

Specialty_CSV: ab,bc,cd

What I have right now is:

.... && Query1[Specialty] IN CalculateTable(Allselected(Query1[Specialty_CSV], Filter(Query1, Query1[Provider ID])) ....

Above is rightly trying to match the ab,bc,cd in the Specialty column and hence fetches nothing. How can I re-write the above to make it work so that it becomes: 

... && Query1[Specialty] IN {"ab", "bc", "cd"} for a given provider.

How can this be re-written?

Thanks a lot!

[Greg_Deckler]

Please post some sample data that can be copied and pasted. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[ppgandhi11]

Thanks Greg. It is quite difficult to explain entire scenario, that is why i provided a basic sample data.

Let me try to elaborate with more sample.

Let us assume below is my record set.

Provider	Specialty	Specialty_CSV
P1	AB	AB,BC,CD
P1	CD	AB,BC,CD
P2	AB	AB
P3	AB	AB,PQ
P3	AB	AB,PQ
P3	PQ	AB,PQ
P4	BC	AB,BC
P4	BC	AB,BC
P4	AB	AB,BC
P5	XY	XY, YZ

Based on this, I want to find for a given Provider on the drill through page, the count of rows for other providers who are associated with any of the given provider's specialty_csv value. This is used for benchmarking.

For example, P1 is associated with AB, BC, CD based on its specialty_csv value. So my reference point is AB, BC, CD for all the providers who are contributors for these specialties other than P1.

Even though P3 has a specialty PQ, it is still part of calculation for it because P3 is associated with at least one of the AB, BC, CD based on its Specialty.

However, P5 is not part of benchmarking for P1 because P5 is not associated with any of the AB, BC, CD.

This is what I have done so far.

Numerator Ins Spec updated2 =
VAR Ins_Spec_Num =
    CALCULATE(
        COUNTROWS(Query1),
        FILTER(
            ALL(Query1),
            Query1[Service Code] = MAX(Query1[Service Code]) &&
   Query1[Specialty] IN CALCULATETABLE(ALLSELECTED(Query1[Specialty_CSV]), FILTER(Query1, Query1[Provider ID])) &&   -- This does not work
            Query1[Provider ID] <> MAX(Query1[Provider ID])
            )
            )
return Ins_Spec_Num

If I hardcode for a given provider the values, then it works. So, below works.

Numerator Ins Spec updated2 =
VAR Ins_Spec_Num =
    CALCULATE(
        COUNTROWS(Query1),
        FILTER(
            ALL(Query1),
            Query1[Service Code] = MAX(Query1[Service Code]) &&
   Query1[Specialty] IN {"AB", "BC", "CD"} &&
            Query1[Provider ID] <> MAX(Query1[Provider ID])
            )
            )
return Ins_Spec_Num

Essentially what i need is: from Specialty_CSV which has AB, BC, CD value, extract "AB", "BC", "CD" and replace it in the IN clause.

I already tried changing specialty_CSV value to: "AB", "BC", "CD" but it gives blank resultset. I think what power bi is trying in that case is to find entire value as a string " "AB", "BC", "CD" " instead of using tokenized value.

Above example should give me the result: 7  (1 for P2 + 3 for P3 + 3 for P4)

Please let me know if this suffice the problem statement.  Thanks much again!

Prashant-

[TomMartens · Accepted solution]

Hey,

I have to admit that I still do not understand what you want.

But I see that you have a string value that looks like this "AB,CD,FG" and that you need this inside a measure in combination with the operatator IN {...}

Knowing that {...} represents a table, we have to convert the string "AB,CD,FG" into a table with one column and three rows representing the values "AB". "CD" and "FG"

This is exactly what happens inside this measure:

Measure = 
var stringSelected = FIRSTNONBLANK('Table1'[String],0)
var stringAsPath = SUBSTITUTE(stringSelected,",","|")
var intPathLength = PATHLENGTH(stringAsPath)
var tempTable = 
    SELECTCOLUMNS(
        ADDCOLUMNS(
            GENERATESERIES(1,intPathLength)
            ,"Specialty",PATHITEM(stringAsPath,''[Value],TEXT)
        )
        ,"Specialty", [Specialty]
    )
return
CONCATENATEX(
    tempTable
    ,[Specialty]
    ," - "
)

The idea behind this, is to substitute the "," with the "|" character, this allows to use the PATH-functions. PATHLENGTH counts the numbers of members inside the path.
GENERATESERIES creates a table with n-rows, together with ADDCOLUMNS and PATHITEM, it's possible to extract the items from the string.
What you would need is the table variable tempTable, created based on a textinpunt, in the measure above this input is coming from the variable stringInput. To demonstrate the working of the DAX statement I just use the table variable tempTable as input for the iterator functon CONCATENATEX(...).

Here is a screenshot that shows the input, the column "String" and the measure

Wondering if this is what you are looking for.

Regards,

Tom