Issue with Groupby

[amikm]

Hi,

I am trying to write below DAX for a measure which is not working for me as per requirement-

TestMeasure =
VAR a =
    COUNTROWS (
        FILTER (
            SUMMARIZE (
                'Transaction',
                'Transaction'[Pname],
                'Transaction'[Pcode],
                'Transaction'[Status],
                'Product'[ProductID]
            ),
            'Transaction'[Status] = "Y"
                && 'Transaction'[Code] = 1
        )
    )
VAR b =
    CALCULATE ( DISTINCTCOUNT ( 'Product'[ProductID] ) )
RETURN
     ( a / b ) / 12

The challenge is Transaction and Product tables are not directly connected to each other, but it is present in the model and there might be some indirect connection between these tables.

I am not supposed to change the relationship that exists in the model, I need to correct the above DAX so that I can use 'Product'[ProductID] in my GroupBy function.

Whenever I am trying to write a DAX as above, I am not able to see Product[Id] in GroupBY (), it's only taking few other tables that are related to the Transaction table.

Please advise, how can I achieve the above DAX, if I want to do GroupBy with the columns that are present in two separate tables and there is no direct relationship between the tables.

Thanks

[amitchandak · Accepted solution]

@amikm ,

TestMeasure =
VAR a =
COUNTROWS (

SUMMARIZE (FILTER ('Transaction'[Status] = "Y"
&& 'Transaction'[Code] = 1
),
'Transaction',
'Transaction'[Pname],
'Transaction'[Pcode],
'Transaction'[Status],'Product'[ProductID]

)

)
VAR b =
CALCULATE ( countx (values('Product'[ProductID]), 'Product'[ProductID] ) )
RETURN
( a / b ) / 12

[amikm]

Thanks, @amitchandak , I am not able to understand the FILTER () as we can give a table or expression that results in table to a filter function, right?

[v-xiaoyan-msft]

Hi@amikm，

FILTER() returns a table that represents a subset of another table or expression. You can use FILTER to reduce the number of rows in the table that you are working with, and use only specific data in calculations. FILTER is not used independently, but as a function that is embedded in other functions that require a table as an argument.

Hope it helps.

Best Regards,

Caitlyn Yan