Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

DAX Help

Hi,

Need some help with DAX. I have data something like below (second screenshot). Total Current Hours and 'Measure3' are the 2 metrics.

Total current hours has data for multiple regions (column3) for specific set of Task ID where as Measure is only associated with one Region for those specific Task IDs. In this case Measure3 is associated with 'Office of the General Counsel'. When I try to show of the data in a single table, Measure 3 is repeated. What is required that is that I should have only record for 'Measure3' for the corresponding Region name (firstscreenhot would be the ideal output). 

The DAX I have is 

Measure3 = CALCULATE([Total Mission Hours],
FILTER(Fact_Mission_Hours,Fact_Mission_Hours[Region Name]=SELECTEDVALUE('Dim_Region (2)'[Region Name DB]))).
 
Please suggest.

 

Need Help

7 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    7 years ago

    Without seeing the model it is tricky to help.  Give this a try.

    Measure3 =
CALCULATE (
    [Total Mission Hours],
    KEEPFILTERS (
        FILTER (
            Fact_Mission_Hours,
            Fact_Mission_Hours[Region Name]
                = SELECTEDVALUE ( 'Dim_Region (2)'[Region Name DB] )
        )
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi,

      Below is the model. The thing is that there is no relation between Dim Region and Fact Current Hours. If I create a relationship between the fact current hours table gets filtered for the region from the fact mission hours. Not sure, if a left join can be defined which can help me get the values I want.

      The formula you sent me is giving me the same results.

      • jdbuchanan71's avatar
        jdbuchanan71
        Super User
        7 years ago

        We might be able to get this without the relationship.  Give this a try.

         

        Measure3 = 
VAR TheList = VALUES ( 'Fact Current Hours'[Column 3] )
VAR TheFilter = VALUES ( 'Dim_Region (2)'[Region Name DB] )
RETURN
    CALCULATE (
        [Total Mission Hours],
        INTERSECT ( TheList, TheFilter )
    )

        Intersect only allows tables, not values so I could not use SELECTEDVALUE but if you have a filter set on 'Dim_Region (2)'[Region Name DB] that will feed in only the single selection and flow into the INTERSECT