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 make column optional / selective

 

Hello, so I have a line graph. on the y-axis is a set of data set by a spesific unit. I would like to be able to switch from the current unit of data to a diffrent unit of data, is there a way to change over without editing in the visualizations tab under " Values". Currently I have created two columns that convert the data to one unit or the other manualy .

End goal is to let the user choose what units they would like to see the data presented in , ex: inches or cm. 

 

All help is great! thanks. 

 

     -  Collin

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@ryan_mayu

 

Ok , so i figured it out! it was way more simple that what we were trying. I noticed you were using 

VAR amount=sum(""Column"")  to basicaly transfer a column, so i did this 
 
Bar/Line Value =
VAR mAamount = SUM('BCI - id 1,3,6,7,8'[mA])
VAR amount=SUM('BCI - id 1,3,6,7,8'[dBuA])
return
IF(selectedvalue(Table1[Units]) ="dBuA", amount,
IF(selectedvalue(Table1[Units]) ="mA" , mAamount,0
))
 
I created two columns one for each unit that calulated the number correctly. Then the code imports them depending on what is selected. Here are the two calculated columns::
 
dBuA = IF((NOT('BCI - id 1,3,6,7,8'[Unit] = "dBuA")) , 20 * LOG10('BCI - id 1,3,6,7,8'[RequirementLevel]) + 60  ,
             'BCI - id 1,3,6,7,8'[RequirementLevel])
 
mA = IF((NOT('BCI - id 1,3,6,7,8'[Unit] = "mA")), (POWER(10,(( 'BCI - id 1,3,6,7,8'[RequirementLevel] - 60)/20))),
             'BCI - id 1,3,6,7,8'[RequirementLevel])
"front end" table to select what unit to display"front end" table to select what unit to display "back end"  the table"back end" the table
 
thanks for the help , hope this explanation is not as confusing

View solution in original post

13 REPLIES 13
ryan_mayu
Super User
Super User

@Anonymous

 

I created two tables to test.

 

c1.JPGc2.JPG

 

 

measure = 
VAR meterchangerate=CALCULATE(SELECTEDVALUE(Sheet10[CHANGE RATE]),FILTER(Sheet10,Sheet10[UNIT]="meter"))
VAR decimetre=CALCULATE(SELECTEDVALUE(Sheet10[CHANGE RATE]),FILTER(Sheet10,Sheet10[UNIT]="decimetre"))
VAR cm=CALCULATE(SELECTEDVALUE(Sheet10[CHANGE RATE]),FILTER(Sheet10,Sheet10[UNIT]="cm"))
VAR amount=sum(Sheet9[AMOUNT])
return if(SELECTEDVALUE(Sheet10[UNIT])="meter",amount*meterchangerate,if(SELECTEDVALUE(Sheet10[UNIT])="decimetre",amount*decimetre,if(SELECTEDVALUE(Sheet10[UNIT])="cm",amount*cm)))

You can create a measure and filter unit to change the value.

 

c1.JPGc2.JPG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@ryan_mayu

Thankyou for the responce, it kind of makes sense, but how exectly are you doing your calculations to go from 2 to 20. 

 

the calculations im using are alittle  more complex, here are the two i need. 

 

 mA to dBuA = 20 * LOG10( table1'[ to dBuA] ) + 60
dBuA to mA  = POWER(10,(( table1'[ to mA]  - 60)/20))
 
thanks for the help. 
   -Collin

@Anonymous

 

2 or 20 depends on what unit you choose. That's why you see two tables. One is created for unit conversion. You stored all the units in that table. It will automatically pick out the corresponding conversion rate when end users choose one unit.

 

Will the number of 20 will changed in your DAX? Maybe you can modify the coding like something below

 

 

mA to dBuA =
VAR unitonerate=calculate(selectedvalue(.....)   -- when end user choose unit one, what conversion rate will be 
VAR unittworate=calculate(selectedvalue(...)  -- when end user choose unit two, what conversion rate will be
VAR unitthreerate=calculate(selectedvalue(...) -- when end user choose unit three, what conversion rate will be
Return
if(selectedvalue ()="UnitOne",  unitonerate* LOG10( table1'[ to dBuA] ) + 60,   -- use unitone conversion rate in the calculation
if(selectedvalue()="UnitTwo",  unittworate* LOG10( table1'[ to dBuA] ) + 60,  -- use unittwo conversion rate in the calculation
if(selectedvalue()="UnitThree",unitthreerate** LOG10( table1'[ to dBuA] ) + 60, --use unitthree conversion rate in the calculation
 
The coding is not completed. You need to modify it.
 
Please let me know if you have any other questions. Thanks
 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@ryan_mayu

 

Hello, not sure if you saw my last post  but I have figured some things out. my only and final question is for your CHANGE RATE column you have a number. I need to use my equasion 

20 * LOG10( 'BCI - id 1,3,6,7,8'[RequirementLevel]) + 60

I tried putting this in place for the column "Change Rate" but it does not display.  here are some pictures. 

 

dfgdg.PNG

 

 

this is the code that works        

 

 

dffffffff.PNGHere is one table

@Anonymous

 

Is '20*LOG10('BCI-ID1,3,6,7,8'[RequirementLevel])+60' a measure?

 

please try below coding. Thanks

 

Value =

VAR dBua=20*LOG10('BCI-ID1,3,6,7,8'[RequirementLevel])+60

VAR mA=POWER(10,(('bci-id1,3,6,7,8'[RequirementLevle])

 

if(selectedvalue(Table1[units])="dBua", amount*dBua,if(selectvalue(Table1[units]="mA", amount*ma))





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@ryan_mayu

 

The VAR will not let me refrence the column, is there a dax formula maybe to put it inside that would make it work?

thanks   -  Collin

@Anonymous

 

Could you please some sample data? I am not quite sure what's the difference between my example and your data.

 

How you get the '20*LOG10('BCI-ID1,3,6,7,8'[RequirementLevel])', I am not familar with that. 

 

What I have provided you is the coding in measure. You can try it by creating a measure. 

 

Thanks





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@ryan_mayu

 

yes, sorry . the '20*LOG10('BCI-ID1,3,6,7,8'[RequirementLevel])' is the equasion to go from mA to dBuA and vice versa for the other direction, the example where you have 1, 10 , 100 is where i need these equasions. in your example your changing units by factor of 10 or 100 , im changing by these equasions 

 

mA to dBuA - 20 * LOG10('BCI - id 1,3,6,7,8'[RequirementLevel]) + 60

 

dBuA to mA - POWER(10,(( 'BCI - id 1,3,6,7,8'[RequirementLevel] - 60)/20))

 

the  'BCI - id 1,3,6,7,8'[RequirementLevel] is simply my column of data ( your "Amount column" in your example)

 

Side Note :: the data entered into the column is a mix of dBuA and mA  , do I need to change all the default data to one or the other for this to work

 

Requirment LevelUnit
106dBuA
100dBuA
90dBuA
90dBuA
106dBuA
106dBuA
106dBuA
100dBuA
20mA
60mA
60mA
33.3333mA
100mA
100mA
66.6666mA
200mA
200mA
90dBuA
90dBuA
106dBuA
106dBuA

here is a sample of data

 

the data is mixed for ease of user  adding data

 

thanks.

 

@Anonymous

 

I am not sure I understand your scenario clearly.

 

I create a column to converse all the unit to one. For example, I choose dBua

 

dBuA = if(Sheet19[unit]="dBuA",Sheet19[requirement level],20*LOG('Sheet19'[requirement level])+60)

 

c1.JPG

 

Then you just create a unit table which only has mA and eBuA in order to  filter your data.

 

amount = sum(Sheet19[requirement level])
 
 
change = if(SELECTEDVALUE(Unit[Unit])="dBuA",[amount],POWER(10,(([amount]-60)/20)))

c2.JPGc3.JPG

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@ryan_mayu

 

Ok , so i figured it out! it was way more simple that what we were trying. I noticed you were using 

VAR amount=sum(""Column"")  to basicaly transfer a column, so i did this 
 
Bar/Line Value =
VAR mAamount = SUM('BCI - id 1,3,6,7,8'[mA])
VAR amount=SUM('BCI - id 1,3,6,7,8'[dBuA])
return
IF(selectedvalue(Table1[Units]) ="dBuA", amount,
IF(selectedvalue(Table1[Units]) ="mA" , mAamount,0
))
 
I created two columns one for each unit that calulated the number correctly. Then the code imports them depending on what is selected. Here are the two calculated columns::
 
dBuA = IF((NOT('BCI - id 1,3,6,7,8'[Unit] = "dBuA")) , 20 * LOG10('BCI - id 1,3,6,7,8'[RequirementLevel]) + 60  ,
             'BCI - id 1,3,6,7,8'[RequirementLevel])
 
mA = IF((NOT('BCI - id 1,3,6,7,8'[Unit] = "mA")), (POWER(10,(( 'BCI - id 1,3,6,7,8'[RequirementLevel] - 60)/20))),
             'BCI - id 1,3,6,7,8'[RequirementLevel])
"front end" table to select what unit to display"front end" table to select what unit to display "back end"  the table"back end" the table
 
thanks for the help , hope this explanation is not as confusing

Hi @Anonymous,

 

It seems that you have solved your problem, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@Anonymous Glad to hear that. You are welcome.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@ryan_mayu

 

Sorry to confuse you, i am using the Unit column to display on a graph,

so basicaly the chart that you created is good, but I need it to affect the graph as well.

 

I can manualy change the graph by putting the corresponding column in the " values" slot.  but I need a way to have a user do that same thing once the page is published. 

 dffffffff.PNG 

 

Sorry to make this difficult, im trying to learn as I go. 

greatly appreciate the help! 

 

 - Collin

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.