1

I have a table with multiple date columns. As we cannot use two separate x axis is same chart, I am creating a separate table with required data.

My old table has a categorical column "label" and a date column "date". My new table has a "date" column, and I want the following data,

Count of distinct labels from old_table, where old_table.date == new_table.date

Basically, the distinct equivalent of following query,

FOOBAR = COUNTROWS(FILTER(OLD_TABLE[LABELS], OLD_TABLE[DATE] = NEW_TABLE[DATE]))

I can't use CALCULATE, as that does not support comparing two columns to each other. I can't use COUNTROWS with FILTER, as that does not give distinct count. I can't use DISTINCTCOUNT, as that doesn't support FILTER.

I also tried GROUPBY, as follows

TABLE = GROUPBY(OLD_TABLE, OLD_TABLE[DATE].[Date], "A", COUNTX(CURRENTGROUP(), DISTINCT(OLD_TABLE[LABELS])))

This gives the error, "Interface not implemented". However it works if I remove DISTINCT.

What is an alternative?

Kreeeeee
  • 35
  • 5

1 Answers1

1

If I understand your request, you are trying to create a new calculated table with two columns, the Date and the count of the distinct Labels for that Date in the source table.

So I created a small table with DAX, to be used for tests, like follows

OLD_TABLE = DATATABLE (
    "Date", DATETIME,
    "Label", STRING,
    {
        { "2020-01-01", "A" },
        { "2020-01-01", "A" },
        { "2020-01-01", "B" },
        { "2020-01-01", "D" },
        { "2020-01-01", "E" },
        { "2020-01-02", "A" },
        { "2020-01-02", "B" },
        { "2020-01-02", "C" }
    }
)

Applying the DAX code that follows we get the desired calculated table.

NEW_TABLE =
ADDCOLUMNS (
    ALL ( OLD_TABLE[DATE] ),
    "Count",
        CALCULATE (
            DISTINCTCOUNT ( OLD_TABLE[Label] )
        )
)

enter image description here

sergiom
  • 4,791
  • 3
  • 24
  • 32
  • This works, but how do I extend this to more columns? I have multiple date columns in the old_table, and in the new_table, I want this count with respect to each date column. Worst case, I think I can create new table for each date column, and then combine them into new table, but is there an easy way to do so? – Kreeeeee Dec 07 '20 at 04:22
  • Hi, Kreeeeee. In this question you wrote: "My old table has a categorical column "label" and a date column "date".", so what you are asking with this comment is a different question. I suggest you to post a new question with my sample OLD_TABLE DAX creation code modified with more than one date. Also write a clear description of what you are trying to achieve, and we will be glad to help also on this new question. :-) Also, consider to reward the contributors by upvoting and accepting the answers when correct/useful :-) – sergiom Dec 07 '20 at 08:29
  • 1
    I have posted a new question [here](https://stackoverflow.com/questions/65181238/powerbi-how-to-get-distinct-count-for-a-column-in-a-table-while-grouping-for-m) – Kreeeeee Dec 07 '20 at 11:52