select multiple values in slicers to search, find or containsstrings, then correctly

[jianlong]

Hi all,

Here is a trick question and hope someone can help.      Two tables:   Client and ICD as below screenshot.  I was able to create measures and count client for a single selection, such as if I select A01 in slicer, I will have client count 2, and B01 gets 3.  

Search_label =
var label= search(selectedvalue(Slicers[ICD]),selectedvalue(Client[ICD]),,blank())
return
if(label>0,"Find","Not")

Client_count =
countrows(filter(Client,[Search_label]="Find"))

my question is how to make mulitiple ICD selections, for example, if I select A01 and B01 , the count will be 4; if I choose C01, C03, I get count 1,  if I choose C01,C02, C03, I get count3.  etc..   

Completely lost now, please kindly help, sorry that I could not load the file, but here is the link, hope you can download and give it a try .   thanks a lot

Client count trial file in google drive 

[jianlong]

Thanks for your reply Ashish. I will give it a try and see how it goes.  For the real dataset, there will be around 50M rows of records, with ICD, and other codes concatenate together for mulitple columns, if we create scalar values for each ICD and  other columns, there will be probably 500M records or even more if considering the combinations.  This is the reason why we started without creating any relationship and put all the ICD string together.   Best,

[Ashish_Mathur · Accepted solution]

Hi,

You may download my solution file from here.

Hope this helps.

[_Sarah_]

Hi Ashish,

is it possible for you the repeat the solution sharing?

[Ashish_Mathur]

I do not have the file.  Share some data to work with, explain the question and show the expected result.

[_Sarah_]

Hi Ashish, I have a multi-select slicer (named Clients) that I want to use for filtering the table rows acting on the Customers column.

For example, looking at the image, the selected rows should be the ones pointed. 

Thanks for your support.

Sarah

[Ashish_Mathur]

Hi,

I am not sure how much i can help but i'd like to try.  Share the download link of the PBI file.

[_Sarah_]

Hi,

I cannot share the PBI file because contains sensible data.

I try to post my question as a new topic hoping someone had the same issue.

Thanks for you attention!  

[jianlong]

Hi Ashish, Thank you very much, could you please post the dax measure?  For whatever reason, my 2020 Dec version is unable to open your file.   Appreciate your help. 

[Ashish_Mathur]

Hi,

The DAX measure is a simple DISTINCTCOUNT() function.  What's important is the transformation which i used in the Query Editor.