Forum Discussion

chat_peters's avatar
chat_peters
Icon for Helper III rankHelper III
4 years ago
Solved

Help with measure aggregating rows based on values in two columns

Hello,

I posted this question and I keep coming back here because the version of SSAS I am using combined with direct query have a lot of limitations. I have the below data model, when I pick a location from CallerDim I'd like that location to filter every row in Order Fact where that selected location is equal to either Caller Location or Buyer Location.  

I can't use IN operator or containsstring. I made a concatenated column and used containsstring which works great in power bi desktop but doesn't work at all in SSAS in direct query. For reference the below measure works in power bi desktop, but I need a workaround as containsstring doesn't work in SSAS direct query. This measure achieves my goal but I can't put this into production.

Measure that works = var locations_x = IF(HASONEVALUE(CallerDim[Caller location]),VALUES(CallerDim[Caller location]))
                                        RETURN CALCULATE(
                                                    COUNTROWS(
                                                            FILTER(
                                                                    'Order Fact',CONTAINSSTRING('Order Fact'[Concatcol],locations_x))),
                                                                    ALL(CallerDim))

 

I created the measure below, it works partially but it's too dynamic, I can't get a correct total for the orders.

Totalordersnew = var locations = IF(HASONEVALUE(CallerDim[Caller location]),VALUES(CallerDim[Caller location]))
                                RETURN CALCULATE(
                                            COUNTROWS(
                                                    FILTER('Order Fact',
                                                        OR(locations='Order Fact'[Caller Location],
                                                           locations='Order Fact'[Buyer Location]))),
                                                            All(CallerDim))

Please please help, thank you for taking the time to read this post.

Link to Power BI File 

 

Data model

 

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    I think CONTAINSSTRING is a newer function. Try using SEARCH instead like this:

     

    VAR locations_x =
    IF (
        HASONEVALUE ( CallerDim[Caller location] ),
        VALUES ( CallerDim[Caller location] )
    )
RETURN
    CALCULATE (
        COUNTROWS ( 'Order Fact' ),
        FILTER (
            VALUES ( 'Order Fact'[Concatcol] ),
            SEARCH ( locations_x, 'Order Fact'[Concatcol], 1, 0 ) > 0
        ),
        ALL ( CallerDim )
    )

     

    Note: I've also changed the measure so that it filters on a single column instead of a table.

     

    Reference: https://www.sqlbi.com/articles/from-sql-to-dax-string-comparison/

2 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    I think CONTAINSSTRING is a newer function. Try using SEARCH instead like this:

     

    VAR locations_x =
    IF (
        HASONEVALUE ( CallerDim[Caller location] ),
        VALUES ( CallerDim[Caller location] )
    )
RETURN
    CALCULATE (
        COUNTROWS ( 'Order Fact' ),
        FILTER (
            VALUES ( 'Order Fact'[Concatcol] ),
            SEARCH ( locations_x, 'Order Fact'[Concatcol], 1, 0 ) > 0
        ),
        ALL ( CallerDim )
    )

     

    Note: I've also changed the measure so that it filters on a single column instead of a table.

     

    Reference: https://www.sqlbi.com/articles/from-sql-to-dax-string-comparison/

  • chat_peters's avatar
    chat_peters
    Icon for Helper III rankHelper III
    4 years ago

    Thank you so much! this fixed another issue where the other filters weren't giving me the right results. Brilliant! Thank you a million times 🙂