Count DAX function

[johnpjustus]

All,

I have a table like this with sample data

I wanted to display some KPI values in a Card visualization.

Example : Count of records where status = Closed  and so on.

Also I would like to keep a slicer for Checklist name only with Year by removing the LCA Inspection so users can slice on the year of the check list name.

Can some one please help me to achieve this? 

Inspection date	Site	Checklist name	Status
3/16/2024	Memphis	LCA Inspection - February 2024	Closed
3/17/2024	York	LCA Inspection - February 2024	Action in Progress
3/18/2024	Toledo	LCA Inspection - February 2024	Pending inspection
3/19/2024	Perris	LCA Inspection - February 2024	Under inspection
4/15/2024	Memphis	LCA Inspection - March 2024	Closed
4/16/2024	York	LCA Inspection - March 2024	Action in Progress
4/17/2024	Toledo	LCA Inspection - March 2024	Pending inspection
4/18/2024	Perris	LCA Inspection - March 2024	Under inspection

Thank you,

John

[_AAndrade · Accepted solution]

Hi @johnpjustus,

Here is my solution:
1. KPI Cards:

I'm using this two measures:

Count Closed = 
    CALCULATE(
        COUNTROWS(T_DataKpi),
        T_DataKpi[Status] = "Closed"
    )


Count In Progress = 
    CALCULATE(
        COUNTROWS(T_DataKpi),
        T_DataKpi[Status] = "Action in Progress"
    )

2. Checklist name only with Year

I add a new column on power query, that only returns de last 4 characters of Checklist name.

I'm using this M script:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZBRC4IwEMe/ytizQklZPYoQBAVC9RDiw3KHDm2T23zo22e6ILGRTxu3+/9u90tTepC6gdwIJQlnBqhHz6I/4hLyqhbaEMkefd0w02qaeSldhv6JYVc7waMphe5uxzgiXyyf7OGOLcMnCRbB6s2rlQY+xDc2flNYzclGw4OQJEFVIGj7ja3lXFQNXM0hJSC5kEWH+jQNpJ0lJYA4b5+r5IATztqPmj9iukF5+ctKaLMuK6OgS8nGQtxKRhiXj63FuH2MMFMZ2Qs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Inspection date", type date}}),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Changed Type", "ChecklistName", each Text.End([Checklist name], 4), type text)
in
    #"Inserted Text After Delimiter"

This is the part of the M code that add a new column only with the Year.
#"Inserted Text After Delimiter" = Table.AddColumn(#"Changed Type", "ChecklistName", each Text.End([Checklist name], 4), type text)

In your code, you need to change #"Changed Type" to the name of your previous step.

[johnpjustus]

Thank you much!!

I need a small adjustment, actually I need the month as well, like March 2024 and so on from the M script.

Also, the card is displaying as Blank when I put the count closed dax. 

The card displays blank only when I select something from the checklistname slicer and another slicer called operatingEntityGroup, like below:

If I unselect these slicers then the dax works and the card displays data. I guess the card displays the  complete count of status = closed in the table. But I want the count to be dynamically displayed based upon the selection from the slicers. Is it possible?

Thanks,

John

[_AAndrade]

Do you have data for the filter applied?

You could not have closed status...

If you can Share and pbix or more pictures so I can take a look

[johnpjustus]

The count works, for those filters applied there is no data and thats why the card displayed as Blank.

However, for other filters, the count is displaying wrong, it just displays the complete records where status is closed and not taking the filter data.

Also, is it possible to get the updated M code to extract Month and year from ChecklistName?

Thank you,

John

[johnpjustus]

It looks like the problem is after adding the new column using M-code, the number of records kind of doubling and thats why the count displays wrong..

[_AAndrade]

@johnpjustus,

To have a column with Month and Year use this script on Power Query:

Table.AddColumn(#"Inserted Text After Delimiter", "Month_Year", each Text.AfterDelimiter([Checklist name], " - "), type text)

Change #"Inserted Text After Delimiter" to the name of your previous step. The output would be this:

With this data set (is taken from you example) if I select February 2024 from my slicer my card visual show me 1 Closed and 1 In progress and that are the correct answers. See this image:

I don't know what is going wrong with your data set. In order to help you, I need more information and the better way is a pbix file with same data if it's possible to you.

[johnpjustus]

Hi Andrade - Sorry for coming back again. Forgot to ask before.

So I have an requirement like this, please see the screen shot below.

I have attached the data here and is it possible to achieve this format in PBI?

The first field is Column E in the spread sheet. Score is calculation based upon the results (Answer field in the spread sheet) in the next 3 fields.  

I don't have an option to attach the spread sheet, any idea as to how I can attach spread sheet?

[johnpjustus]

I don't think I woud be able to attach excel, so here is the sample data.

[_AAndrade]

Hi @johnpjustus ,

Please open a new ticket and take a look at this post:
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1717...

It can help you publish your example easily.

[johnpjustus]

Thank you so much and I will go over the post.

[johnpjustus]

Thank you so much, I will continue to do some testing and modify the M-code accordingly.