Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mpbems
Frequent Visitor

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=SelectedDropdown2),""))

 

Any insight would be appreciated; thank you!

1 ACCEPTED SOLUTION

@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 🙂


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

15 REPLIES 15
Ashish_Mathur
Super User
Super User

Hi,

Does this measure work?

Measure = selectedvalue(Data1[text1])&", "&selectedvalue(Data2[text1])

If not, then share some data (in a format the can be pasted in an MS Excel file) and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur I've updated the info I shared in an earlier post. What I'm trying to do is create a visible schedule based on user input. In the example below, column A is a list of visitors and columns C through P represent locations they are scheduled to visit on a particular day.  I'm looking to select a week, and a location, and view all scheduled visitors on that given day of the week.

Initially I had numbers listed but they should actually be locations.

 

Row 26 includes the slicers, and row 29 is the desired table layout (column headers in line 28).

 

mpbems_0-1676305035355.png

Hi,

Share data in a format that can be pasted in an MS Excel file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-zhangti
Community Support
Community Support

Hi, @mpbems 

 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures or Excel. I look forward to your response.

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

image.png

Data from Visitor through Saturday2 columns appear the exact same way in the source (Sharepoint list).

Visitors can select more than one location per day, which is why there are two columns per day of week.

When I select a week and location, I'm looking to see all visitors scheduled for each day, whether it's their first or second stop.

There are a few hundred location IDs and maybe around 100 visitors but I only added a few to show how it works currently in Excel.

AllisonKennedy
Super User
Super User

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


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

@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]),"")

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


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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

 

mpbems_0-1676319700746.png

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.

@mpbems  The slicers will come automatically - see this file and let me know if it's what you want. 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

@AllisonKennedy this is great! I think I need to revise how my data is collected, since that is probably making this more difficult than it should be.

This is how my data is currently recorded:

 

mpbems_0-1676381189955.png

 

Instead of submitting their schedules in one line, I'm going to separate them out so it's one entry per day of week and then I should be able to get the results I'm looking for. Thank you!

 

@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 🙂


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

@AllisonKennedy one last thing - sorry to be a pain! In the sample file, when I select the first week (2/12/23 - 2/18/23) it only shows Monday/Tuesday/Thursday since that's where the matching data is found. Is there a way to always show the Sunday through Saturday columns, and a blank field when there is no match? 

@mpbems In the sample file that's possible because it's using a dimension table for the days of the week - just click 'show items with no data' in the visualizations pane

 

AllisonKennedy_0-1676519535769.png

 

 

If you don't have a dimension table for days of the week and are just pulling it from your one table, that won't work. Dimension tables are key! 🙂


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Wow is all I can say...I'm looking at the applied steps and there's no way I would've been able to figure all that out...thank you again!

I ended up rewriting my collection method in PowerApps to match the format you provided, but I may keep both until I can (hopefully) figure out the applied steps on my own. Thank you again!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors