Evaluate dax query in the cube database

Prathibha Kambhampati 21 Reputation points
2021-04-15T18:46:35.567+00:00

Hi, I am very new to ssas and hope you guys help me out here. We have some DAX queries and wanted to validate the data in the cube database. This is my dax query

COUNTX('Fact User Interaction',IF(RELATED('Line Item Type'[LineItemType])="UserVehicle",'Fact User Interaction'[LineItemTypeKey]))

How do I evaluate this in the database. I have searched a lot in google and couldn't find any straight answer and is so confusing. Would you please help me? Also please suggest any Dax documentation. Thank you so much in advance!

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,007 questions

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Lukas Yu -MSFT 5,796 Reputation points
    2021-04-16T09:19:25.6+00:00

    You could evalute this and see this in Excel, there is an button shows "analysis in Excel"

    For your expression, you could see similar scene at this article which will tell you clearly how to use the expression : DAX COUNT and COUNTX – Impact of Using Measures V’s Calculated Columns

    As DAX documentation, normally you could find examples and guides at microsoft's documentation. https://learn.microsoft.com/en-us/dax/

    If you want to learn DAX systematically, I would recommend the dax bible : The Definitive Guide to DAX by the very dax creator. You could easily google this book.

    regards,
    Lukas

  2. Prathibha Kambhampati 21 Reputation points
    2021-04-16T14:49:49.727+00:00

    Thank you Lukas for your response. I will check it out the DAX documentation links you have provided.

    Actually I tried in excel, the value it is showing in excel not matching with database. not sure how to debug this. Please share if any other ways. Thank you. I really appreciate.

  3. Prathibha Kambhampati 21 Reputation points
    2021-04-20T20:59:54.457+00:00

    Cube - formula - COUNTX('Fact User Interaction',IF(RELATED('Line Item Type'[LineItemType])="UserVehicle",'Fact User Interaction'[LineItemTypeKey])) giving results: 378

    where as in the data warehouse, I wrote this query to validate, this one gave results: 53

    SELECT COUNT(distinct fpt.LineItemTypeKey)
    FROM AtlasDW.DW.FactUserInteraction fpt
    JOIN AtlasDW.DW.DimLineItemType dlitItemType
    ON fpt.LineItemTypeKey = dlitItemType.LineItemTypeKey
    WHERE lineitemtype='UserVehicle'

    Is this clear? let me know if any other details required

    Thank you so much in advance!

  4. Prathibha Kambhampati 21 Reputation points
    2021-04-21T22:00:10.197+00:00

    I have figured out that my query on database not taking count of transactions that belongs to line item type instead i was taking count of lineitemtype. Now I am getting the correct result when taking count of transactions on fact. Anyhow thank you for your help. Dax documentation guide is very clear and very useful. Thank you