Forum Discussion

hemann's avatar
hemann
Helper I
4 years ago
Solved

DAX query combining data

Hi, I am trying to create the below visual. I have two pieces of data that I need to combine to get the correct results (Data 1 & Data 2). I am ideally looking to create a DAX query to achieve this. I have created a third piece of data, which is the result needed. Can somebody please help with a neat measure!!

Data1

Data2

Data Result

 

 

Need Help
  • hemann's avatar
    hemann
    4 years ago

    I tweaked the solution to return a volume of 1 for all records. This was more or less the solution I was looking for, thanks for your help with it. 

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below DAX formula and the attached pbix file.

    It is for creating a new table.

     

    Result table =
VAR _newtable =
    CROSSJOIN ( VALUES ( Data[Feature] ), VALUES ( 'Product'[Product] ) )
VAR _addcolumns =
    ADDCOLUMNS (
        _newtable,
        "@Result",
            IF (
                COUNTROWS (
                    FILTER (
                        Data,
                        Data[Feature] = EARLIER ( Data[Feature] )
                            && Data[Product] = EARLIER ( 'Product'[Product] )
                    )
                ) <> 0,
                MAXX (
                    FILTER ( 'Product', 'Product'[Product] = EARLIER ( 'Product'[Product] ) ),
                    'Product'[Material]
                ),
                "No Material"
            ),
        "@Volume",
            MAXX (
                FILTER (
                    Data,
                    Data[Feature] = EARLIER ( Data[Feature] )
                        && Data[Product] = EARLIER ( 'Product'[Product] )
                ),
                Data[Volume]
            ) + 0
    )
RETURN
    _addcolumns
    • hemann's avatar
      hemann
      Helper I
      4 years ago

      I tweaked the solution to return a volume of 1 for all records. This was more or less the solution I was looking for, thanks for your help with it.