Search text cross tables and return result

[Anonymous]

Hi heros,

Can you please help to check whether it's possible to accomplish following.

Table instruction and sample file are at the bottom.

The expected result is:

Create a slicer or table, in the upper visual (Filtered Performance) displays value from RAW table:

If "Service" is selected, then show all related result if any value in RAW[Query] contains a value in Service Dept[Key Queries]

If any of 2, e.g. "Serivce" and "Parts" are selected, then show all related result if any value in RAW[Query] contains a value in either Service Dept[Key Queries] or Parts[Key Queries]

If ALL (or none) is selected, show all related result if any value in RAW[Query] contains a value in any of 3 dept Tables.

In the below visual (General Performance Bar Chart), display ALL result in the RAW table no matter what is selected in Department.

Tables:

- RAW table: include all raw data, terms, searches, inquires, date

- 3 indivisual dept. tables:  maintained by each department, contains their own key concerned queries:

 

- Dept table: 3 unique values:

Don't know why I cannot upload a file as attachment, so the sample Power BI data is here:

https://filebin.net/8p0z5a5wi69k6pel

Thanks in advance.

H

[Anonymous]

May I ask for a help to this last time? Will not re-post reply myself again~

thanks community for the understanding.

[v-kelly-msft]

Hi  @Anonymous ,

First create 3 columns in Raw table:

Query in Service = 
CONCATENATEX('Service Dept',
IF(SEARCH(FIRSTNONBLANK('Service Dept'[Key Queries],1),'RAW'[Query],1,0)<>0,
'Service Dept'[Key Queries],""))

Query in Parts = 
CONCATENATEX('Parts Dept',
IF(SEARCH(FIRSTNONBLANK('Parts Dept'[Key Queires],1),'RAW'[Query],1,0)<>0,
'Parts Dept'[Key Queires],""))

Query in Software = 
CONCATENATEX('Software Dept',
IF(SEARCH(FIRSTNONBLANK('Software Dept'[Key Queries],1),'RAW'[Query],1,0)<>0,
'Software Dept'[Key Queries],""))

Then create 2 measures as below:

_Inquries = 
var _table=ADDCOLUMNS('Dept',"_Inquries",
SWITCH('Dept'[Department],
"Service",CALCULATE(SUM('RAW'[Inquires]),FILTER(ALL(RAW[Query in Service]),'RAW'[Query in Service]<>BLANK())),
"Parts",CALCULATE(SUM('RAW'[Inquires]),FILTER(ALL(RAW[Query in Parts]),'RAW'[Query in Parts]<>BLANK())),
"Software",CALCULATE(SUM('RAW'[Inquires]),FILTER(ALL(RAW[Query in Software]),'RAW'[Query in Software]<>BLANK()))))
Return
IF(CALCULATE(COUNTROWS('Dept'),ALLSELECTED(Dept))<3&&CALCULATE(COUNTROWS('Dept'),ALLSELECTED(Dept))>0,SUMX(_table,[_Inquries]),CALCULATE(SUM('RAW'[Inquires]),FILTER(ALL(RAW),'RAW'[Query in Parts]<>BLANK()&&'RAW'[Query in Service]<>BLANK()&&'RAW'[Query in Software]<>BLANK())))

_Searches = 
var _table=ADDCOLUMNS('Dept',"_Searches",
SWITCH('Dept'[Department],
"Service",CALCULATE(SUM('RAW'[Searches]),FILTER(ALL(RAW[Query in Service]),'RAW'[Query in Service]<>BLANK())),
"Parts",CALCULATE(SUM('RAW'[Searches]),FILTER(ALL(RAW[Query in Parts]),'RAW'[Query in Parts]<>BLANK())),
"Software",CALCULATE(SUM('RAW'[Searches]),FILTER(ALL(RAW[Query in Software]),'RAW'[Query in Software]<>BLANK()))))
Return
IF(CALCULATE(COUNTROWS('Dept'),ALLSELECTED(Dept))<3&&CALCULATE(COUNTROWS('Dept'),ALLSELECTED(Dept))>0,SUMX(_table,[_Searches]),CALCULATE(SUM('RAW'[Searches]),FILTER(ALL(RAW),'RAW'[Query in Parts]<>BLANK()&&'RAW'[Query in Service]<>BLANK()&&'RAW'[Query in Software]<>BLANK())))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi @v-kelly-msft , thank you for the great effort and time.

I may not clarify clearly, what the expected result, is not only show result by Time. But also show all possible related result after filtering / using slicer.

For example, when "Parts" is selected, if I create another visual, that is "By Key Queries By Date", can show the data for each "Key Queries", and the "Key Queries" in Axis will change upon the selection of "Department".

But current way is not working for this.

Maybe create Custom Measure will help?

[v-kelly-msft]

Hi @Anonymous ,

How to calculate the values if the query in Raw table have more than 1 key value in a row?

For example,for inquries 29,it has 2 key queries ,Premier and TESLA,how to define the value for each key query?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi Kelly @v-kelly-msft ,

I took some time to make the sample in Excel as clarification. Please check following link.

Excel Sample 

The expected result is can create measure and make it able to be applied in any type of visual.

To reply your question: if a Query contains more than one "Key Queries", the Searches and Inquires shall be counted in each of "Key Queries"

For single "Key Queries":

But for all, (contains any), will not use direct sum, but use formular like:

So you can see the Total is less than summarzie of above each Key Queries, which means: if a Query contains more than 1 value in "Key Queries", will be counted only 1 time.

Then, in Power BI, hopefully the created measure can be applied to any visual, for example: I can see each queries (category) by weekNum or Day (columns)

So, I think to create new measure to sum if a value in Query matches any value in "Key Queries", will be better.

Hopefully above explanation can help you help me on this.

Thank you again for the ideas, help and your time.

H

[Anonymous]

Updated the attachment URL, and May I reply this and ask for a help?

Thanks.