locked
Adding static meber to DAX row context. RRS feed

  • Question

  • Hi, I've encountered an issue with designig a measure in DAX.

    Scenario is straighfoward: I do have an Attribute with members (0,1,2,3)

    I want that if user selects any of (1,2,3) it should filter to required value but also include 0.

    So, basiaclly adding a static member to a row filter,

    WorkOrder Source          Key       Date

    1               CRM       CRM1      1

    1               CRM       CRM2      2

    1               CRM       CRM3      3

    1               APRE      APRE1     0

    1               APRE      APRE2     0

    1               POL       POL1      0

    1               POL       POL2      0

    Expected result would be (TSQL behaviour)

    SELECT (1,2,3) AS DateAttribute

    UNION (WITHOUT ALL)

    SELECT 0 AS DateAttribute

    Any help would be much appreciated.

    What I have right now (it's completely wrong) is

    WorkOrderCount:

    =CALCULATE(


    COUNTROWS

    (DISTINCT(Fact[WorkOrder])),

    FILTER


    (



    UNION

    (ROW("Date",0)),

    VALUES

    ('Date'[Date])              

    )

    )







    Monday, April 4, 2016 8:39 PM

Answers

  • Hi,

    The problem is that ROW(“Date”,0) has no lineage, so has no effect as a filter when combined with values from the Date column itself.

    As you are using a version of DAX with UNION, something like the below should work:

    =

    CALCULATE (     

        COUNTROWS ( DISTINCT ( Fact[WorkOrder] ) ),

        UNION (

            CALCULATETABLE ( VALUES ( 'Date'[Date] ), 'Date'[Date] = 0 ),

            VALUES ( 'Date'[Date] )

        )

    )

    • Marked as answer by Marc James X Tuesday, April 5, 2016 10:45 AM
    Tuesday, April 5, 2016 5:18 AM

All replies

  • Hi,

    The problem is that ROW(“Date”,0) has no lineage, so has no effect as a filter when combined with values from the Date column itself.

    As you are using a version of DAX with UNION, something like the below should work:

    =

    CALCULATE (     

        COUNTROWS ( DISTINCT ( Fact[WorkOrder] ) ),

        UNION (

            CALCULATETABLE ( VALUES ( 'Date'[Date] ), 'Date'[Date] = 0 ),

            VALUES ( 'Date'[Date] )

        )

    )

    • Marked as answer by Marc James X Tuesday, April 5, 2016 10:45 AM
    Tuesday, April 5, 2016 5:18 AM
  • Works perfectly! The code will be used in Power BI model. Many thanks.
    Tuesday, April 5, 2016 10:46 AM