cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

How to create a dynamic Column

Hello,

I have the following problem. I have a table like such:

 CLIENT DATE TARGET SALES ON DEP. A SALES ON DEP. B AMADOR 01/02/2022 aaaa 10 0 JOHN 01/02/2022 xxxx 12 13 JACK 02/02/2022 xxxx 5 12 MARIA 03/02/2022 xxxx 5 5 AMADOR 02/02/2022 aaaa 0 4 AMADOR 05/02/2022 aaaa 0 6 MARIA 05/02/2022 cccc 4 0 BLAIR 06/02/2022 cccc 5 2 JACK 06/02/2022 cccc 0 2

let's say I want to show that table as a matrix, without the column "DATE". "DATE" will beocme a slicer, and I want to create a second slicer called "STATE" which has the following logic:

- if sum(dep A)>0 and sum(dep B)>0 ---> state = shared

- if sum(dep A)>0 and sum(dep B)=0 ---> state = only A

- if sum(dep A)=0 and sum(dep B)>0 ---> state = only B

- if sum(dep A)=0 and sum(dep B)=0 ---> state = other

If I do the logic explained earlier as a calculated measure it works fine, as the measure recalculates itself when the date filter changes. However, when I put the same code as a calculated column, it doesnt respond to the date filter. The idea is, if for example, the date range selected is: 01/02/2022 to 03/02/2022 the table should be something like this:

 CLIENT TARGET sum(DEP. A) sum( DEP. B) STATE AMADOR aaaa 10 4 shared JOHN xxxx 12 13 shared JACK xxxx 5 12 shared MARIA xxxx 5 5 shared

However, if the date selection was 02/02/2022 to 05/02/2022, the output table should be:

 CLIENT TARGET sum( DEP. A) sum( DEP. B) STATE AMADOR aaaa 0 10 only B JACK xxxx 5 12 shared MARIA xxxx 5 5 shared MARIA cccc 4 0 only A

The reason for which I need "STATE" to be a calculated column and NOT a measure is becasue I need to display it as a filter pane. Please help! I have tried to create a DAX table, but doesnt work, I have tried to create a "map" table with one column with the different possible values of state (let's call it column aux) and try to create a relationship between 'aux' and the calculated measure but of course that doesnt work either... please help!

1 ACCEPTED SOLUTION
Community Support

The clear answer, slicer or filter pane does not work for any table in the data view. The data always from data table to page never than turn back.

If you want to put the STATE in the filter pane , you can put the measure in to filter pane on this visual directly. it works.

1. And create a table contains all the result of STATE measure , put the table in the slicer,
2. then compare the values selected in the slicer with the result of STATE measure, a new measure named IF, if the same then 1 else blank.
3. Put the IF measure in filter pane.

But, if you have many visuals on this page, you need to add them one by one.

Best Regards

Community Support Team _ chenwu zhu

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

2 REPLIES 2
Frequent Visitor

Yes!! I ended up doing somehting of the sort, so thank you! I hope the post helps many people as well 🙂

Community Support

The clear answer, slicer or filter pane does not work for any table in the data view. The data always from data table to page never than turn back.

If you want to put the STATE in the filter pane , you can put the measure in to filter pane on this visual directly. it works.

1. And create a table contains all the result of STATE measure , put the table in the slicer,
2. then compare the values selected in the slicer with the result of STATE measure, a new measure named IF, if the same then 1 else blank.
3. Put the IF measure in filter pane.

But, if you have many visuals on this page, you need to add them one by one.

Best Regards

Community Support Team _ chenwu zhu

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

Announcements

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors