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
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.

    • jdbuchanan71's avatar
      jdbuchanan71
      Super User
      7 years ago

      Can you share your .pbix file?

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi,

        This is confidential data and I am afraid I won't be able to share the file with you. Any thing else can help you understand the data?