CODEWITHSUNDEEP
powerbidaxdaxstudio
LeppyR64
LeppyR64

Reputation: 5359

Countrows in Summarized DAX Table Variable

I have the following simplified DAX measure. The real world example does a lot of heavy lifting to end up at the information that's in var t1

Here's the example for set up in DAX Studio:

EVALUATE

var t1 = DATATABLE("item", STRING, {{"a"}, {"b"}})

return
    ADDCOLUMNS(
        SUMMARIZE(
            t1,
            [item]
        ),
        "count", calculate(countrows(t1))
    )

This query returns a table with a count of 2 for each item, a and b.

If I were to create the simplified table in the data model and use the query it would return the correct result with a count of 1 for each item, a and b.

I understand that the reason is that t1 is a constant and that the two references to the t1 in the query are seperate references and therefor the filter context is different on the two instances.

Is there a way to do this using the table var t1 or do I need to solve this in a different method?

Current incorrect output:
[item],[count]
a,2
b,2

Expected output:
[item],[count]
a,1
b,1

Upvotes: 0

Views: 5492

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40224

You could use a FILTER like this:

ADDCOLUMNS (
    SUMMARIZE ( t1, [item] ),
    "count", COUNTROWS ( FILTER ( t1, [item] = EARLIER ( [item] ) ) )
)

Using GROUPBY is cleaner for this sort of thing, IMO.

GROUPBY ( t1, [item], "count", SUMX ( CURRENTGROUP(), 1 ) )

Upvotes: 2

Related Questions