Forum Discussion

mpbems's avatar
mpbems
Frequent Visitor
3 years ago
Solved

Stuck with DAX - need to replicate Excel formula

I'm stuck trying to replicate an Excel formula in Power BI and I'm completely stumped.   This is what it looks like in Excel:   TEXTJOIN(", ",TRUE,IF((data1=SelectedDropdown) * (data2=SelectedDro...
Need Help
  • AllisonKennedy's avatar
    AllisonKennedy
    3 years ago

    mpbems 

     

    If you click 'Transform Data' in the sample file I sent through, you'll notice that I've used your current data collection format and transformed it for you, so up to you if you change the collection method or not - let me know if you still are getting stuck, otherwise hope there's a solution in one of these replies for you 🙂

AllisonKennedy's avatar
AllisonKennedy
Icon for Community Champion rankCommunity Champion
3 years ago

mpbems  It looks like you need to reference value of slicers? 

Power BI measures will do this, columns will not. Unfortunately you need to be in a column for this measure, so can you provide more info about how you want to use it?

 

I think something like this will work:

 

Measure = 
MAXX(

Table, 
COMBINEVALUES ( ", ", IF( (data1= SELECTEDVALUE ( Table[SelectedDropdown] ) ) * (data2=SelectedDropdown2),"")

 

I'm not sure what your IF statement is doing or what your SELECTED dropdowns are but hopefully I've given you enough clues to get started, report back with any issues.

mpbems's avatar
mpbems
Frequent Visitor
3 years ago

AllisonKennedy when I enter the code, it looks like I'm a parenthesis or two short.

 

NamesMon =
MAXX(
    FieldSchedInput,COMBINEVALUES(", ",IF(([Week]=SELECTEDVALUE(DateByWeek[Week])) * ([SearchMon1]=SELECTEDVALUE(MBRDN[Name1]),"")
  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    3 years ago

    mpbems Sorry, that was just meant to be a sample.

     

    Is the data you provided the raw data? Or can you get it in a different format?

     

    If it's the raw data, I would unpivot it first in Power Query - Unpivot the other columns after selecting the Visitor and Week Column. 

     

    Then Split column > by non-digit digit to remove the numbers from the days of week. 

     

    Finally, use one of my favorite functions, CONCATENATEX: 

     

    Visitors = 
    CONCATENATEX( Table, Table[Value], ", ")

     

    Put that in a matrix with Attribute in the Columns and Week in the rows. Add your slicers for Location.

     

    You can rename Attribute and Value to better names that make sense to you of course. 🙂 

     

    At some stage you may consider getting dimension tables, but we'll leave that for another post. 

    • mpbems's avatar
      mpbems
      Frequent Visitor
      3 years ago

      AllisonKennedy I think this is a better representation of what I'm trying to accomplish:

       

      One slicer will include the weeks, while the other will include all locations in a dropdown.

      I'm looking to select a week and a location, and view all visitors at that week and location on each day of the week. I know I'll need at least 7 columns but I can't quite get the right formula.

      CONCATENATEX sounds like the way to go, but I'm not sure of the right format to use to look at the two slicer selections.