Tag Archives: isblank

Identify products with no sale in DAX

Posted on by • Posted in Basic script, DAX • Tagged , • Leave a comment

To identify products with no sale in DAX we can use the ISBLANK function.

Here is a sample

EVALUATE
FILTER (
    SUMMARIZE (
        'Product',
        'Product'[Product Name],
        "Sales", 'Internet Sales'[Internet Total Sales]
    ),
    ISBLANK ( 'Internet Sales'[Internet Total Sales] ) = TRUE
)

And if you want the products with sale you can switch “TRUE” with “FALSE”

EVALUATE
FILTER (
    SUMMARIZE (
        'Product',
        'Product'[Product Name],
        "Sales", 'Internet Sales'[Internet Total Sales]
    ),
    ISBLANK ( 'Internet Sales'[Internet Total Sales] ) = FALSE
)

Hierarchy testing in DAX (attempt number 2)

Posted on by • Posted in Advanced • Tagged , , , , , , • Leave a comment

Here is some new query samples on how DAX hierarchies work. And this time it will work 🙂

Id did a little modification of the model. I had to make a new hierachy in the employee table.

Hierachy in tabular model

Then you will have to deploy the model to the server.

My sample query looks like this:

DEFINE
    MEASURE 'Employee'[Employeelevel] =
        PATHLENGTH (
            PATH ( Employee[EmployeeKey], Employee[ParentEmployeeKey] )
        )
    MEASURE 'Employee'[ParentEmployee] =
        LOOKUPVALUE (
            'Employee'[Last Name],
            'Employee'[EmployeeKey], PATHITEMREVERSE (
                PATH (
                    'Employee'[EmployeeKey],
                    'Employee'[ParentEmployeeKey]
                ),
                2,
                1
            )
        )
    MEASURE 'Employee'[PathReverse] =
        PATHITEMREVERSE (
            PATH (
                'Employee'[EmployeeKey],
                'Employee'[ParentEmployeeKey]
            ),
            2,
            1
        )
    MEASURE 'Employee'[Path] =
        PATH (
            'Employee'[EmployeeKey],
            'Employee'[ParentEmployeeKey]
        )
EVALUATE
FILTER (
    SUMMARIZE (
        'Employee',
        'Employee'[EmployeeKey],
        'Employee'[Last Name],
        "Sales", 'Reseller Sales'[Reseller Total Sales],
        "Path", 'Employee'[Path],
        "PathItemReverse", 'Employee'[PathReverse],
        "Employeelevel", 'Employee'[Employeelevel],
        "ParentEmployee", 'Employee'[ParentEmployee]
    ),
    ISBLANK ( 'Reseller Sales'[Reseller Total Sales] ) = FALSE
)
ORDER BY 'Employee'[ParentEmployee], 'Employee'[EmployeeKey]

And the result looks like this

hierarchy correct

To verify the result I made an Excel pivot.