Counting Uni-char Characters

[Anonymous]

Hi Experts

Is it possibel to count the number of unichar characters? I am tyring to count the number of X and ticks and X i have against a products. In order to plot the count in a pie chart.

My DAX for unichar

Status =

VAR Tick = UNICHAR(10004)

VAR Cross = UNICHAR(10006)

VAR Dash = UNICHAR(10134)

RETURN

SWITCH( TRUE(),

DH_AUDITCONTENTIMPORT[Days Overdue] >= 0 && DH_AUDITCONTENTIMPORT[Days Overdue] <= 2 , Tick ,

DH_AUDITCONTENTIMPORT[Days Overdue] > 2 && DH_AUDITCONTENTIMPORT[Days Overdue] <= 7 , Dash,

DH_AUDITCONTENTIMPORT[Days Overdue] > 7 && DH_AUDITCONTENTIMPORT[Days Overdue] <= 100, Cross

)

Image of what i am trying to count...

[TanumoyDalal]

[amitchandak]

@Anonymous 

refer :https://community.powerbi.com/t5/Desktop/UNICHAR-Green-Up-arrow-and-Red-Down-arrow/td-p/367804

https://community.powerbi.com/t5/Desktop/UNICHAR-Green-Up-arrow-and-Red-Down-arrow/td-p/367804

In new release you have an option for icon in Matrix under conditional formatting

[Greg_Deckler]

Right, so create a measure that SUMMARIZE your data just like in the visual, add a column with your UNICHAR characters and then use COUNTX or COUNTROWS and FILTER to count the number rows of interest.

[Anonymous]

Super Greg

You are expectional more smarter then me on this, confused.....do you have a demo pbix, please.

[Greg_Deckler · Accepted solution]

Sure @Anonymous - Here is an example (attached PBIX).

Number of Ticks Measure = 
    VAR __Table = SUMMARIZE('DH_AUDITCONTENTIMPORT',[Column1],"__Status",[Status])
RETURN
    COUNTROWS(FILTER(__Table,[__Status] = UNICHAR(10004)))

[Anonymous]

thanks for all your help greg, God Bless.

[Anonymous]

Also my max days is a meaure not a calculated column as per your pbix file.

[Anonymous]

Greg

Thanks excellent, but how do i plot the 5 dashes and 3 x and four ticks in a pie chart...within a single measure...

[Greg_Deckler]

I do not believe that it matters that your days overdue is a measure.

Man @Anonymous, moving the goal posts!! 🙂

In your case, I would create a disconnected table, let's call it Categories with a single column called Category with the following data:

Tick

Cross

Dash

Use this in a legend on your pie chart. Then a measure like this:

Measure = 
    VAR __Category = MAX('Categories'[Category])
RETURN
    SWITCH(__Category,
        "Tick",COUNTROWS(FILTER('DH_AUDITCONTENTIMPORT',[Status] = UNICHAR(10004))),
        "Cross",COUNTROWS(FILTER('DH_AUDITCONTENTIMPORT',[Status] = UNICHAR(10006))),
        COUNTROWS(FILTER('DH_AUDITCONTENTIMPORT',[Status] = UNICHAR(10134)))
    )

Updated PBIX attached, ignore "Table".

[Anonymous]

Great Greg, how do you get the pie chart to filter the other tables you have added to your PBIX

[Greg_Deckler]

@Anonymous I did it like below. PBIX attached.

Status 2 = 
VAR Tick = UNICHAR(10004)
VAR Cross = UNICHAR(10006)
VAR Dash = UNICHAR(10134)
VAR DaysOverdue = MAX('DH_AUDITCONTENTIMPORT'[Days Overdue])
VAR Symbol = 
    SWITCH( TRUE(),
        DaysOverdue >= 0 && DaysOverdue <= 2 , Tick ,
        DaysOverdue > 2 && DaysOverdue <= 7 , Dash,
        DaysOverdue > 7 && DaysOverdue <= 100, Cross
    )
VAR Category = MAX('Categories'[Category])
RETURN
    SWITCH(TRUE(),
        Category = "Tick" && Symbol = Tick,Tick,
        Category = "Cross" && Symbol = Cross,Cross,
        Category = "Dash" && Symbol = Dash,Dash,
        BLANK()
    )