Need to change the category and counts

[RasDev1]

Hi all,

I need help finding a solution for the following:

I have a table called "tickets," and one license plate can have multiple tickets. I need to categorize the counts, such as "Number of license plates having 1 ticket" (see attached image).

How can I achieve this? If I filter the dates, the value should change according to the specific time period.

Additionally, I have created two columns for this purpose.

Please let me know how to proceed

1)

Recurring Licenceplate count =

CALCULATE(

    COUNT('tickets'[id]),

    FILTER(

        'tickets',

        'Tickets'[License Plate] = EARLIER('Tickets'[License Plate])

    )

)
2) 

Recurring Category =

IF([Recurring Licenceplate count] = 1, "No of licence plate having 1 ticket",

IF([Recurring Licenceplate count] = 2, "No of licence plate having 2 tickets",

IF([Recurring Licenceplate count] = 3, "No of licence plate having 3 tickets",

IF([Recurring Licenceplate count] = 4, "No of licence plate having 4 tickets",

IF([Recurring Licenceplate count] = 5, "No of licence plate having 5 tickets",

IF([Recurring Licenceplate count] > 5, "No of licence plate having more than 5 tickets"

))))))

[Ashish_Mathur · Accepted solution]

Hi,

I have answered a similar question in the attached file.

Hope this helps.

[v-xuxinyi-msft]

Hi @RasDev1 

Thanks for the reply from SamWiseOwl .

The following is for your reference:

My sample:

Create a measure as follows

Recurring Licenceplate count = 
CALCULATE(
    COUNT('tickets'[id]),
    FILTER(
        'tickets',
        'tickets'[License Plate] = MAX('tickets'[License Plate]) && 'tickets'[Date] IN VALUES(tickets[Date])
    )
)

Recurring Category = SWITCH(TRUE(),
[Recurring Licenceplate count] = 1, "No of licence plate having 1 ticket",
[Recurring Licenceplate count] = 2, "No of licence plate having 2 tickets",
[Recurring Licenceplate count] = 3, "No of licence plate having 3 tickets")

Output:

If you need to dynamically change the data based on the slicer, you need to use measure, but measure will be affected by the context of the visualization, so you need to put the [License Plate] column used in the formula into the visualization.

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RasDev1]

when i remove a licence plate it look like this

but i need each categorize wise,
like no of licence plate having 1 ticket ,2 ticket,3 ticket,.. more then 5 ticket

[RasDev1]

Thank you for sharing this, but I need it to display as shown in the reference image. The count should represent unique license plate counts.

any possible solution for this?

[SamWiseOwl]

Hi @RasDev1 

First create a table with those values listed out:

Put this into your table visual with a measure like this:

Count Tickets =

var licencetable =

SUMMARIZE('tickets','tickets'[licenceplate], "Count licence", DISTINCTCOUNT(tickets[ticket id]))

var final =

SWITCH(

    SELECTEDVALUE('Ticket Table'[Number of tickets])

    ,"Num 1" , COUNTROWS(FILTER(licencetable, [Count licence] = 1))

    ,"Num 2" , COUNTROWS(FILTER(licencetable, [Count licence] = 2))

    ,"Num 3" , COUNTROWS(FILTER(licencetable, [Count licence] = 3))

    ,"Num 4" , COUNTROWS(FILTER(licencetable, [Count licence] = 4))

    ,"Num 5" , COUNTROWS(FILTER(licencetable, [Count licence] = 5))

    ,COUNTROWS(FILTER(licencetable, [Count licence] > 5))

) RETURN final

It should look something like this:

[RasDev1]

Thank you for sharing this, but I need it to display as shown in the reference image. The count should

when i change a date slicer filter it should be dynamically change,

any possible solution for this?

[SamWiseOwl]

Hi @RasDev1 

Did you try it?

It is dynamically changing on mine.

Measures can be recalculated on the fly.