Forum Discussion
Display data from different columns in one column based on slicer
Hi Anonymous ,
In your scenario, there should be a unique column to show the table normally, there is also a unique column in the post you found called catagory, you can try to delete the column and you will find the table cannot show completely.
If you do not have such a column, you can use power query editor to index it, for examle, i use the suffix as the unique column and create a measure like this,
AC/BIC = IF ( SELECTEDVALUE ( 'Choose Account'[Source] ) = "AC", LOOKUPVALUE ( Sheet1[AC], Sheet1[INDEX], SELECTEDVALUE ( Sheet1[INDEX] ) ), IF ( SELECTEDVALUE ( 'Choose Account'[Source] ) = "BIC", LOOKUPVALUE ( Sheet1[BIC], Sheet1[INDEX], SELECTEDVALUE ( Sheet1[INDEX] ) ) ) )BTW, pbix as attached.
Best regards,
Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more
Hi Anonymous ,
In your scenario, there should be a unique column to show the table normally, there is also a unique column in the post you found called catagory, you can try to delete the column and you will find the table cannot show completely.
If you do not have such a column, you can use power query editor to index it, for examle, i use the suffix as the unique column and create a measure like this,
AC/BIC =
IF (
SELECTEDVALUE ( 'Choose Account'[Source] ) = "AC",
LOOKUPVALUE ( Sheet1[AC], Sheet1[INDEX], SELECTEDVALUE ( Sheet1[INDEX] ) ),
IF (
SELECTEDVALUE ( 'Choose Account'[Source] ) = "BIC",
LOOKUPVALUE ( Sheet1[BIC], Sheet1[INDEX], SELECTEDVALUE ( Sheet1[INDEX] ) )
)
)BTW, pbix as attached.
Best regards,
Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more
Thank you so much! I'll check this out and let you know how it works.
v-lid-msft this clearly works great in your sample file, so I'm sure I can port it over to mine. Thank you!
v-lid-msft as it turns out, I accepted this as a solution a bit too soon. There's one piece I can't figure out. In your sample you sent back, you added a column titled "index" which wasn't in my data set. It looks like the index column has some common data (123, 456, 789) which I have listed in both fields of AC and BIC. In reality, that was just my lazy masking of the data, and there's no relation between the AC and BIC columns. So "123" would be meaningless (I think), because it can't relate to both of those columns. In reality, each row of data would look more like AC111 and BIC999 on a row, where there's nothing in common.
Does that make sense, and does it matter? Can this be done using only the columns I sent originally?
thanks!
Hi Anonymous ,
Sorry for my unclear answer, It does not matter that you have different value in AIC/BC columns, this formula should works fine when you have a column contian unique value, we suggest you to add a index column in Power Query Editor like following picture. I use the "123" just because I cannot create index column in your pbix file. You can also use your unique column such as id column, just change the index in the formula.
v-lid-msft That makes perfect sense, I was wondering if that might be the case. This is great, thank you!
The link expired. Can you upload it again, please?
@v-lid-msft Your link expired. Can you share the file again, so we can fully understand your solution, please?
Hi Anonymous ,
You can download the sample pbix file from this link: PBIX File
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello
@v-lid-msft is there a way to use your combined AC/BIC column in a slicer or filter?
Thank you
