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 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 dateSiteChecklist nameStatus
3/16/2024MemphisLCA Inspection - February 2024Closed
3/17/2024YorkLCA Inspection - February 2024Action in Progress
3/18/2024ToledoLCA Inspection - February 2024Pending inspection
3/19/2024PerrisLCA Inspection - February 2024Under inspection
4/15/2024MemphisLCA Inspection - March 2024Closed
4/16/2024YorkLCA Inspection - March 2024Action in Progress
4/17/2024ToledoLCA Inspection - March 2024Pending inspection
4/18/2024PerrisLCA Inspection - March 2024Under inspection

 

Thank you,

John

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.






11 Replies

  • _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