Display data from different columns in one column based on slicer

[Anonymous]

I have a situation where the user needs to select between two possible options (in a slicer, presumably), and based on their selection, we then decide which column should populate the column of a table visual.

For example, consider this very basic data source:

In my table visual, I want the user to be able to select either BIC or AC in a slicer, and then based on their choice, the first column of the visual will be populated by either the BIC or AC column. So if they choose BIC, the data visual displays this data:

And if they choose AC, they see this:

It seems like this shouldn't be too complicated, but I can't quite get it. I did find this post, but can't make it work with my data: https://community.powerbi.com/t5/Community-Blog/Dynamic-Hideable-Columns-in-Power-BI-Table-Visual/ba...

Here's the attached .pbix that I'd like to make work:

And what it looks like now: 

Thanks!

[v-lid-msft · Accepted solution]

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

[Meyes]

Hello 

@v-lid-msft is there a way to use your combined AC/BIC column in a slicer or filter? 

Thank you

[Anonymous]

@v-lid-msft  Your link expired. Can you share the file again, so we can fully understand your solution, please?

[v-lid-msft]

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.

[Anonymous]

The link expired. Can you upload it again, please?

[Anonymous]

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

[v-lid-msft]

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.

[Anonymous]

@v-lid-msft  That makes perfect sense, I was wondering if that might be the case. This is great, thank you!

[Anonymous]

@v-lid-msft  this clearly works great in your sample file, so I'm sure I can port it over to mine. Thank you!

[Anonymous]

Thank you so much! I'll check this out and let you know how it works. 

[d_gosbell]

Your situation is different to the one in the link you provided where they can use measures. What you are trying to do will change a "group by" column so that approach won't work.

I don't think this is possible with a slicer, but you could use 2 bookmark buttons. Simply create 2 copies of your table on top of each other. One with the AC column and the other with the BIC column then create 2 bookmarks. One which hides the AC table and shows the BIC one and another which shows AC and hides BIC and link each of these bookmarks to a button.