Forum Discussion

mm5308's avatar
mm5308
Helper I
4 years ago
Solved

Reference a Column Resulting from a Summarize Function, Within the Same Table Function??

Tried searching for this topic for a while but honestly I'm not even sure how to word what I'm trying to accomplish, so I'll explain it.

 

My main table contains a couple dozen columns and I'm trying to create a summary table to show the relationship between two of them. The first column is BatchID. It's almost a unique key field except that some of the BatchIDs are repeated. That happens when a transaction is recorded in one month then reversed the following month. The second column is of course the MonthNum.

 

I created a summary table as follows:

SumTable = 

SUMMARIZE('Tbl',

'Tbl'[BatchID],

'Tbl'[MonthNum])

This produces, as I want, a table of about 12,000 rows.

 

Here's where the tricky part comes. What I really want to do with this table is add a calculated field that references one of the columns I just created. Technically, if I were to create a table of distinct values of the BatchID field from the main table { = DISTINCT('Tbl'[BatchID]) }, the total number of rows would be 10,000. Again, that's because 2,000 of those BatchIDs are repeated in another month.

 

So what I want to do is: perform what would essentially be a =COUNTIF() function in Excel on the BatchID column from the SumTable I just created. The problem is that I don't know how to reference that column from the SUMMARIZE function because I'm still within the table creation process at that point.

 

To illustrate, this is what I tried:

SumTable =
ADDCOLUMNS(
SUMMARIZE('Tbl',
'Tbl'[BatchID],
'Tbl'[MonthNum]),
"Count",COUNT('Tbl'[BatchID]))
 
I'm sure you can already spot the problem: my COUNT function at the end points to the main table so every row of the SumTable displays the same huge value, which is the count of the BatchIDs from the main table.
 
Is it possible to change that "Count" function to reference not the BatchID field from the main table, but instead reference the BatchID field from the nested SUMMARIZE function?
 
I'm a relative beginner so I'm not sure how to do this, and again, I've tried and researched unsuccessfully.
Need Help
  • davehus's avatar
    davehus
    4 years ago

    Hi mm5308 ,

     

    Try this column measure to see if it gets you the desired outcome.

     

    Count Batches = CALCULATE(COUNTA('Table'[BatchID]),ALLEXCEPT('Table','Table'[BatchID]))
     

     

     

     

8 Replies

  • davehus's avatar
    davehus
    Memorable Member
    4 years ago

    Hi mm5308 ,

     

    Try this column measure to see if it gets you the desired outcome.

     

    Count Batches = CALCULATE(COUNTA('Table'[BatchID]),ALLEXCEPT('Table','Table'[BatchID]))
     

     

     

     

    • mm5308's avatar
      mm5308
      Helper I
      4 years ago

      Yes, that worked perfectly! Thank you davehus! Now I need to learn more about ALLEXCEPT...

      • davehus's avatar
        davehus
        Memorable Member
        4 years ago

        No problem, every day is a school day with PowerBI. 🙂

  • davehus's avatar
    davehus
    Memorable Member
    4 years ago

    Hi mm5308 ,

     

    I'm not sure if this will help as you are saying that there is a countif so you might have an additional clause to add. You could create a measure like below.

    CALCULATE(COUNTROWS(

    SUMMARIZE('Tbl',
    'Tbl'[BatchID],
    'Tbl'[MonthNum])))

     

    Let me know if this is on the right track?

     

    • mm5308's avatar
      mm5308
      Helper I
      4 years ago

      Hi davehus ,

      I probably should've clarified that I'm looking to create a table - not a measure - that displays all ID and MonthNum values, plus that "count" column I want to add. As far as the "COUNTIF" reference, I only brought it up to illustrate what I would do in Excel. In that environment I'm pretty savvy. Translating that knowledge to the "language" of Power BI, however, is where I struggle. Hope this explanation clarifies, and thanks for your input!

      • davehus's avatar
        davehus
        Memorable Member
        4 years ago

        Hi mm5308 , No problem, have you an excel screengrab of your desired output with dummy data and I'll see what I can do for you. 🙂