Forum Discussion

arthur_mq's avatar
arthur_mq
Frequent Visitor
2 years ago

DAX formula - count now working

Hi, I need help with this DAX formula. The table 'New SBLF List' has a column named “Title” and a second column called “ConsentorDiscussion”.

 

I created a DAX formula below to count the times the text "Discussion w/ Presentation" appears under the “ConsentorDiscussion” column. And the then list each text under the “Title” column when the “ConsentorDiscussion” value is "Discussion w/ Presentation".

 

The count shows blank even when there a few values listed under the “ConsentorDiscussion” column equals "Discussion w/ Presentation". I tested the count section of the formula separated, and this works fine, but when I added the second section to get the list then only the list shows but not the count. Any help with this will be greatly appreciated. Thanks

 

Discussion w/Presentation List&Count_M =

"Count: "

&

CALCULATE(

    COUNTROWS('New SBLF List'),  

    FILTER(

        'New SBLF List',

        CONTAINSSTRING('New SBLF List'[ConsentorDiscussion], "Discussion w/ Presentation ")

    )

)

 

&UNICHAR(10)&

//List

CONCATENATEX (

        FILTER ( 'New SBLF List', 'New SBLF List'[ConsentorDiscussion] = "Discussion w/ Presentation" ),

        'New SBLF List'[Title] & UNICHAR(10),

        UNICHAR(10)

    )

    &UNICHAR(10)

Need Help

3 Replies

  • audreygerred's avatar
    audreygerred
    Super User
    2 years ago

    I suggest making a table or a matrix with Title and your count, and have it filtered to "Discussion w/Presentation". This will get you what you are looking for. 

    • arthur_mq's avatar
      arthur_mq
      Frequent Visitor
      2 years ago

      Hi audreygerred 

      The problem is that I must match an existing layout to show this information where the columns are dates and the counts and items listed are based on five different sets of calculations very similar to the criteria on the code above.  The raw data is being pulled from a SharePoint list with the records listed in rows, so I had to rearrange the data using a Matrix to match the layout of the existing document.

      This is what I built so far using a Matrix and a measure with several formulas, but each count at the top must be followed by the list of their corresponding items. As you can see the items are listed after the counts, and not with their corresponding count.

       

      2/28/23

      3/14/23

       

      Discussion w/Presentation: 6

       

      Discussion w/0 Presentation: 4

       

      Consent: 6

       

       

      General Services - Adopt a Resolution to Summarily Vacate and Declare Surplus Land

       

      Establish Authority to Authorize and Award a Construction Manager at Risk Contract for Improvements at Heritage Park Building Project

       

      Hillside Meadows Open Space Vacation

       

      Feasibility Analysis on Tiered Winery Expansion

       

      Tentative-Memo: Recycling Capacity Study

       

      ……

       

       

       

      Discussion w/Presentation: 5

       

      Discussion w/0 Presentation: 8

       

      Consent: 4

       

       

      Approve Cooperative Agreement Stormwater Monitoring Coalition and Authorize Contracts to Implement Projects

       

      Feasibility Analysis on Tiered Winery Expansion

       

      Tentative-Memo: Recycling Capacity Study

       

      Authorization to Issue Request for Proposals and to Negotiate and Award a Contract for Water Quality Monitoring and Reporting Services

       

       

       

      ……