Need to make a Cumulative count but can only count a company once

[EugenioProlog]

I have this metric to count the numbers of meetings that happened inside support tickets on each day:

Qtd Reuniões | Tickets =

CALCULATE(

    COUNTROWS(fct__comercial__atividades__reunioes),

    FILTER(

        fct__comercial__atividades__reunioes,

        fct__comercial__atividades__reunioes[atividade_id] IN

        SELECTCOLUMNS(

            fct__comercial__atividades__reunioes__vinculo__tickets,

            "atividade_id", fct__comercial__atividades__reunioes__vinculo__tickets[atividade_id]

        )

    )

)

I have this metric to display the cumulative ammount, so I can plot the cumulative total on a day axis:

Qtd Reuniões | Tickets Acumulado =

CALCULATE(

    [Qtd Reuniões | Tickets],

    FILTER(

        ALLSELECTED( dim__calendario ),

        dim__calendario[dt_data] <= MAX( dim__calendario[dt_data] )

    )

)

Now I need to create a third metric that is a variation of the second one. I need to count the numbers of different companies that had a meeting. So, for example, if one company had 4 meetings, it will count as 4 to the previous metric, but it will count only as 1 to this new metric.
Here is the relation between my meetings table and my company information:
fct__comercial__atividades__reunioes goes to fct__comercial__atividades__reunioes__vinculo__tickets using atividade_id
fct__comercial__atividades__reunioes__vinculo__tickets goes to dim__cx__tickets using ticket_id
dim__cx__tickets goest to fct__cx__tickets using ticket_id

fct__cx__ticket contains empresa_id (company_id column that is a unique identifier of the company)


 

[v-venuppu]

Hi @EugenioProlog ,

I hope the information provided is helpful.I wanted to check whether you were able to resolve the issue with the provided solutions.Please let us know if you need any further assistance.

Thank you.

[v-venuppu]

Hi @EugenioProlog ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-venuppu]

Hi @EugenioProlog ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @johnt75 for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[johnt75]

I'm not sure if this will work, but you could try opening DAX Query View and running

EVALUATE
SUMMARIZECOLUMNS (
    fct__cx__ticket[empresa_id],
    "@num", [Qtd Reuniões | Tickets]
)

If that gives correct numbers then you could embed it in a measure like

Num companies =
COUNTROWS (
    FILTER (
        SUMMARIZECOLUMNS (
            fct__cx__ticket[empresa_id],
            "@num", [Qtd Reuniões | Tickets]
        ),
        NOT ISBLANK ( fct__cx__ticket[empresa_id] )
    )
)

[EugenioProlog]

That did not work

[johnt75 · Accepted solution]

Try

Num companies =
VAR BaseTable =
    ADDCOLUMNS (
        SUMMARIZE (
            fct__cx__ticket,
            fct__cx__ticket[empresa_id],
            dim__cx__tickets[fct__cx__tickets using ticket_id]
        ),
        "@num", [Qtd Reuniões | Tickets]
    )
VAR GroupedTable =
    GROUPBY (
        BaseTable,
        fct__cx__ticket[empresa_id],
        "@total", SUMX ( CURRENTGROUP (), [@num] )
    )
VAR Result =
    COUNTROWS ( GroupedTable )
RETURN
    Result