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) ...
Need Help
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

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi,

        Still does not work. Am I missing something? Please see below.