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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to create a dynamic Column

Hello,

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

CLIENTDATETARGETSALES ON DEP. ASALES ON DEP. B
AMADOR01/02/2022aaaa100
JOHN01/02/2022xxxx1213
JACK02/02/2022xxxx512
MARIA03/02/2022xxxx55
AMADOR02/02/2022aaaa04
AMADOR05/02/2022aaaa06
MARIA05/02/2022cccc40
BLAIR06/02/2022cccc52
JACK06/02/2022cccc02

 

 

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:

CLIENTTARGETsum(DEP. A)sum( DEP. B)STATE
AMADORaaaa104shared
JOHNxxxx1213shared
JACKxxxx512shared
MARIAxxxx55shared

 

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

CLIENTTARGETsum( DEP. A)sum( DEP. B)STATE
AMADORaaaa010only B
JACKxxxx512shared
MARIAxxxx55shared
MARIAcccc40only 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
v-chenwuz-msft
Community Support
Community Support

Hi @Anonymous ,

 

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.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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

v-chenwuz-msft
Community Support
Community Support

Hi @Anonymous ,

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.