Filter with a variable table in a Calculate function

[Chiniminiz]

Hello,

we want to create an automatic ticket analyse system for internal use. The target is to receive for the top 5 categories of tickets the most used words in the short description. 

We have two tables:

A list of Tickets with the category/service

Ticket	Service
5645623	Office
5645624	Word
5645625	Excel
5645626	Excel
5645627	Excel
5645628	Excel
5645629	Excel
5645630	Word
5645631	Excel
5645632	Office
5645633	Word
5645634	Excel
5645635	Office
5645636	Office
5645637	Office
5645638	Office
5645639	Office
5645640	Office
5645641	Office
5645642	Office

and a list of words per ticket from the tickets shortdescription

Tickets	Text
5645623	Office
5645623	not
5645623	working
5645624	Title
5645624	not
5645624	underlined
5645625	C1
5645625	wrong
5645625	title
5645626	B1
5645626	wrong
5645626	title
5645627	B1
5645627	wrong
5645627	Title
5645628	B1
5645629	C1
5645630	Title
5645631	B1
5645632	Web Service
5645633	Title
5645634	B1
5645635	Office
5645636	Office
5645637	Web Service
5645638	Web Service
5645639	Web Service
5645640	Web Service
5645641	Web Service
5645642	Web Service

We have different customers and need the data per month. This we choose with a drop down visual.

To receive the top 5 categories/services we already made the code, which is working correctly. But when we insert a table with two columns: 1. Words from the short description, 2. the counted words from the short descrition

we receive the wrong counts of words.

Our code until here is:

Count Handling User Short Description #3 Service = 
VAR _Nth = 3

VAR _ReferenceService = ALLSELECTED(SV_INQUIRY[IT_SERVICE])

VAR _ServiceAndTickets = 
    ADDCOLUMNS(
        VALUES( SV_INQUIRY[IT_SERVICE] ),
        "Rank", RANKX( _ReferenceService, [Anzahl Tickets Handling User ohne PW])
    )

VAR _NthService = 
    MINX(_FilterNthProduct, SV_INQUIRY[IT_SERVICE])

RETURN
CALCULATE(
    COUNT(Handling_User[Wert]),
    FILTER(
        SV_INQUIRY,
        SV_INQUIRY[IT_SERVICE] = _NthService
    )
)

When we return the _NthService it is correct. So the Calculate function can't relate between the Handling_User table and the VAR _ServiceAndTickets with the [Rank] I think. 

The dashboard looks like this:

If you need further information, please tell me! 🙂

Please help and thanks in advance.

Best regards 

Patrick

[v-chenwuz-msft]

Hi @Chiniminiz ,

What does the formula for [Anzahl] look like?

Best Regards

Community Support Team _ chenwu zhu

[Chiniminiz]

Hi @v-chenwuz-msft,

this is a measure for distinct count the tickets. The real table has many more tickets, which shouldn't be counted. 

This is like 

DISTINCTCOUNT(Tickets[Tickets])

The question behind all is: Can we connect the threetables 

1. Tickets

2. Ticket-Shortdescription

3. VAR 

VAR _ServiceAndTickets = 
    ADDCOLUMNS(
        VALUES( SV_INQUIRY[IT_SERVICE] ),
        "Rank", RANKX( _ReferenceService, [Anzahl Tickets Handling User ohne PW])
    )

In my mind we should need a variable ranking system of the categories like 3. VAR _ServiceAndTickets which is from 1. Tickets. This we should connect to 2. Ticket-Shortdescription, because we want to get the words, per category of the top 5 categories, but in different measures, that we can get different word lists from them.

Do you think, this is even possible?

Thanks a lot für your expertise.

Best regards 

Patrick - Chiniminiz 

[v-chenwuz-msft]

Hi @Chiniminiz ,

You can try using KEEPFILTERS() to add multiple current filter contexts.

Or please share your pbix file without sensitive information and with the output you want.

Best Regards

Community Support Team _ chenwu zhu