topic Get data rows rather than count of rows? in DAX Commands and Tips

Get data rows rather than count of rows?

daxn00b — Fri, 02 Apr 2021 13:54:03 GMT

Anyone know how to get the data rows rather than count of rows with the example below?

Green Cars = 

VAR color_green = 

SUMMARIZE ( FILTER ( ALL ( Data ), Data[color] = "green" ), Data[computer_id] ) 

VAR type_car = 

SUMMARIZE ( FILTER ( ALL ( Data ), Data[type] = "car" ), Data[computer_id] ) 

RETURN 

COUNTROWS ( INTERSECT ( color_green, type_car ) )

Thanks!

Re: Get data rows rather than count of rows?

amitchandak — Fri, 02 Apr 2021 14:34:02 GMT

daxn00b , Create a table like below

Green Cars = 

VAR color_green = 

SUMMARIZE ( FILTER ( ALL ( Data ), Data[color] = "green" ), Data[computer_id] ) 

VAR type_car = 

SUMMARIZE ( FILTER ( ALL ( Data ), Data[type] = "car" ), Data[computer_id] ) 

RETURN 

INTERSECT ( color_green, type_car )

Re: Get data rows rather than count of rows?

Anonymous — Fri, 02 Apr 2021 18:27:14 GMT

Hi,

You can create another table :

Green Cars =

VAR color_green =

SUMMARIZE ( FILTER ( ALL ( Sheet1 ), Sheet1[Country] = "Canada" ), Sheet1[Index] )

VAR type_car =

SUMMARIZE ( FILTER ( ALL ( Sheet1 ), Sheet1[Country] = "France" ), Sheet1[Index] )

RETURN

UNION(color_green,type_car)

Re: Get data rows rather than count of rows?

daxer-almighty — Fri, 02 Apr 2021 22:34:20 GMT

A DAX measure CANNOT return a table. It can only return a scalar value. You can create a table via DAX but it will not be returned by a measure. It can only be created as a table in the model and will be STATIC once created.