Forum Discussion

johnpjustus's avatar
johnpjustus
Helper IV
2 years ago
Solved

Count DAX function

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 ke...
Need Help
  • _AAndrade's avatar
    _AAndrade
    2 years ago

    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.






_AAndrade's avatar
_AAndrade
Resident Rockstar
2 years ago

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's avatar
johnpjustus
Helper IV
2 years ago

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's avatar
    _AAndrade
    Resident Rockstar
    2 years ago

    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's avatar
      johnpjustus
      Helper IV
      2 years ago

      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's avatar
        johnpjustus
        Helper IV
        2 years ago

        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..